Table as parameter in SQL Server

Question

I have the following stored procedure:

 ALTER procedure [dbo].[jk_insertAllLocation]
     @locationTbl as locationTable readonly,
     @TableName varchar(100)
 as
    declare @tbl as locationTable, @sql nvarchar(max)
    begin
    set @sql = N'insert into '+ @tbl + 'select * from ' + @locationTbl +
    'delete c
    from @tbl  c 
    inner join'+ @TableName + 'ON '+@TableName+ '.location_id=c.location_id'
    exec sp_executesql @sql

    set @sql= N'insert into'+ @TableName+ '(location_id,name,address,latitude,longitude,distance,state,sub_cat,id_cat,icon_link,checkinsCount,IDSearch)
    select * from '+ @tbl
    exec sp_executesql @sql

    select id,location_id from lebanon
  end

I keep getting:

Must declare the scalar variable "@tbl".
Must declare the scalar variable "@locationTbl".

and they are already declared..

I think the problem is that they are tables not string to be concatenated but I have to pass the table name as parameter @TableName and I'm using this table name in 2 separate queries using inner join and insert so how can I do that? I'm not finding anything on google

Am I missing something here?

Try to place the `declare` statement inside the `begin/end` block — Andomar, Dec 02 '13 at 15:03
@Andomar nop it does not work :) i always put the declare outside the begin/end block and it works fine ... i don't think that's the problem — User7291, Dec 02 '13 at 15:05
I'm not quite awake yet, but I'm pretty sure you're going to have a problem trying to concatenate a `locationTable` (`@tbl`) into an `nvarchar(max)` variable (`@sql`). — LittleBobbyTables - Au Revoir, Dec 02 '13 at 15:07
@LittleBobbyTables yeah i think that's the problem but i have to pass the table name as parameter `@TableName` and i'm using this table name in 2 separate equerries using `inner join` and `insert` so how can i do that? i'm not finding anything on google — User7291, Dec 02 '13 at 15:13
Why are storing locationTable twice? also the syntax of your declare looks wrong. — Josh, Dec 02 '13 at 16:15
@tbl is null, so that makesyour whole concatentated statement null. However, I woudl not want to send in table names to a proc like this, that is sure sign of bad database design if you feel the need to do this. — HLGEM, Dec 02 '13 at 16:32

score 0 · Answer 1 · answered Dec 02 '13 at 15:21

0

Move the line where you declare those variables after the begin line. There are other problems, too, but I think this will at least change the error message to something more helpful.

Also, this scares me as potentially vulnerable to sql injection attacks.

answered Dec 02 '13 at 15:21

Joel Coehoorn

399,467
113
570
794

Table as parameter in SQL Server

1 Answers1