Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-08 : 10:16:39

Sam writes "/*
I am trying to create a comma delimited string based on a query with an IN clause that refers to a parameter.
*/
declare @temp varchar(4000)
--(these are the parameters)
declare @intItemNumber int, @Batches varchar(4000)
set @intItemNumber = 1273045
set @Batches = '6,8,12,16,20'
--(end of parameter setup)
set @temp = ''
SELECT @temp =(@temp + ltrim(str(a.BatchKey))+ ',' )
FROM vwItemBatchInfo a
WHERE a.ItemNumber = @intItemNumber
-- AND a.BatchKey IN ( @Batches )
GROUP BY a.BatchKey
SELECT @temp = left(@temp,len(@temp)-1)
print 'The batches for this item number :'
print @temp

/*
with the AND line commented out, the sproc works (except it returns too many records)

trying it by executing a string, I get what looks like scope errors.
*/

declare @strQuery varchar(4000)
declare @temp varchar(4000), @Batches varchar(4000)
declare @intItemNumber int
set @intItemNumber = 1273045
set @Batches = '6,8,12,16,20'
set @temp = ''
set @strQuery = '
SELECT @temp =(@temp + ltrim(str(a.BatchKey))+ '','' )
FROM vwItemBatchInfo a
where a.ItemNumber = @intItemNumber
and a.BatchKey in (' + @Batches + ')
group by a.BatchKey
'
exec(@strQuery)
SELECT @temp = left(@temp,len(@temp)-1)
print 'The batches for this item number :'
print @temp

/*
give these errors:
Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@temp'.
Server: Msg 536, Level 16, State 3, Line 14
Invalid length parameter passed to the substring function.

Is it possible to do this? (assign a value (inside an exec) to a variable from outside the exec)
*/"

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-01-08 : 10:32:26

In a word no!

If you really need the value of the "external" variable inside the dynamic SQL then you can store it in a table:-

create table temp_table (temp_var varchar(4000))
insert into temp_table select #temp

Then access the table within your dynamic sql. Since you are updating temp then change your select to an update:-

set @strQuery = '
UPDATE temp_table SET temp_var=temp_var+
(SELECT ltrim(str(a.BatchKey))+ '',''
FROM vwItemBatchInfo a 
where a.ItemNumber = @intItemNumber 
and a.BatchKey in (' + @Batches + ')
group by a.BatchKey)
'

Then, if you need, to set the variable again:-

select @temp=temp_var from temp_table

============
The Dabbler!

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-01-08 : 10:49:37

Just thought of an important point...

I take it you are trying to cycle through a table to generate the csv string; ie.

value
=====
1
2
3

generates

1,2,3

If so, then there are a number of issues to contend with. Firstly the above queries will not do the job. You need a way of moving from one line to the next. This can be done with a cursor or a temporary table (I'll not go into the debate of which is better here - its been covered before on this forum!).

Take a look at some of the following for some help:-

http://www.sqlteam.com/item.asp?ItemID=256
http://www.sqlteam.com/item.asp?ItemID=2368

============
The Dabbler!

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-08 : 11:26:32

Hi sam,

This link shows you how to use sp_executesql and output parameters to accomplish this:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=11000

You will want to look at this one as suggested by David as well

http://www.sqlteam.com/item.asp?ItemID=2368

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-08 : 21:12:48

How about just using the collate function for building? I know there are some good examples of it out there, but can't seem to find any at the moment (I'm on a very slow connection, and don't have SQL books online installed on this system).

Also, if you're on SQL2K, it's always better to use table variables than temp tables (well, at least for manageable data sizes)

Off the top of my head, I would think it would be something like:

DECLARE @tblTemp TABLE (temp_var varchar(4000))

insert into @tblTemp select @temp

select @temp=COLLATE(a.BatchKey,',','''')
from vwItemBatchInfo a
where a.ItemNumber=@intItemNumber
and a.BatchKey in (select temp_var from @tblTemp)

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources