Must declare the scalar variable '%s'.

Question

Post reply

Must declare the scalar variable '%s'.

New2SQL-343122

Default port

Points: 1461
More actions
September 9, 2009 at 7:06 am

#156193

Hi All,
Declare @table1 as Table(cID bigint,Uid bigint)
insert into @table1 select 23,40
insert into @table1 select 20,50
insert into @table1 select 23,60
declare @groupid nvarchar(10)
set @groupid = '40,50'
declare @sql nvarchar(1000)
set @sql = 'select * from ' + @table1 + ' where usergroup_id in ( ' + @groupid + ')'
exec sp_executesql @sql
while executing above script, T-SQL throwing an error as
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@table1".
Any help on fixing the issue ?
Thanks,
Mohan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

the table variable @table1 is not know within the scope of the dynamic SQL you are executing.

I assume you are doing it like this for the IN condition ?

Use the following code to split that string, then you can join to the list.

drop table #t1

go

create table #t1

(

RowId integer,

ValueStr nvarchar(50)

)

go

insert into #t1 values(1,'1, 2 , 3,4')

insert into #t1 values(2,'4')

insert into #t1 values(3,'25,26,27,28,29,30,31,32')

insert into #t1 values(4,'2 ')

go

Drop function CutCsv

go

Create function CutCsv(@CSVString varchar(max))

returns table

as

return(

with Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),

Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),

Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),

Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),

Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),

SpacesPos(sIndex)

as

(

Select n+1

from nums

where n spacesPos.SIndex)-1

from spacesPos

)

Select Item = ltrim(rtrim(substring(@CSVString,StartPos,(EndPos-StartPos))))

from cteSpaceDelta

where EndPos is not null

)

go

Select RowId from #t1 cross apply dbo.CutCsv(ValueStr) ValuesOut

where ValuesOut.Item = '2'

Clear Sky SQL
My Blog[/url]

matt6288 SSCertifiable Points: 5058 More actions · Answer 2

Not sure about the internals but changing it to use a temp table instead of a table variable worked for me.

CREATE TABLE #table1 (cID bigint,Uid bigint)

insert into #table1 select 23,40

insert into #table1 select 20,50

insert into #table1 select 23,60

declare @groupid nvarchar(10)

set @groupid = '40,50'

declare @sql nvarchar(1000)

set @sql = 'select * from #table1 where uid in (' + @groupid + ')'

PRINT @sql

EXEC sp_executesql @sql

DROP TABLE #table1

My guess would be that sp_executesql doesn't have scope on the table variable.

New2SQL-343122 Default port Points: 1461 More actions · Answer 3

Declare @table1 as Table(cID bigint,Uid bigint)

insert into @table1 select 23,40

insert into @table1 select 20,50

insert into @table1 select 23,60

declare @groupid1 nvarchar(10)

set @groupid1 = '40,50'

declare @t1 as table (uid1 bigint)

insert into @t1 select 40

insert into @t1 select 50

declare @sql varchar(1000)

-- set @sql = 'select cID from #table1 where Uid in ( ' + @groupid1 + ')'

select cID from @table1 where Uid in (select uid1 from @t1)

Thanks, I understood the scope of the table variable calling into the exec procudure.

However, my intention is not to use the temp table inside the procedure there I have created the above script for my usage.

Thanks once again.

Regards,

Mohan

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

New2SQL (9/9/2009)
However, my intention is not to use the temp table inside the procedure

Why not? Both Temp Tables and Table Variables start out in memory and both spill over into TempDB if they get too large.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 5

New2SQL (9/9/2009)
Hi All,
Declare @table1 as Table(cID bigint,Uid bigint)
insert into @table1 select 23,40
insert into @table1 select 20,50
insert into @table1 select 23,60
declare @groupid nvarchar(10)
set @groupid = '40,50'
declare @sql nvarchar(1000)
set @sql = 'select * from ' + @table1 + ' where usergroup_id in ( ' + @groupid + ')'
exec sp_executesql @sql
while executing above script, T-SQL throwing an error as
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@table1".
Any help on fixing the issue ?
Thanks,
Mohan

Actually, unless I'm mistaken, your original problem didn't have anything to do with scope, it had to do with syntax. You're refering to a table variable like it's a scalar variable, which is incorrect. You were basically trying to concatenate a string with a table stuck in the middle. That doesn't work.

Frankly I'm' a bit confused on what you're trying to achieve in the first place. Even if you could do what it looks like you're trying to do, the field usergroup_id isn't even a field in the table variable you just created. If you need to dynamically specify the table name, then you likely will need dynamic sql. If you're only doing it so that you can use IN with your string, there are other ways to tackle that problem.

It would help us if you explained exactly what you need to do, as that example code is extremely misleading.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]