Issues with the dynamic query - 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

Transact-SQL (2000)

Issues with the dynamic query

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-05 : 20:41:08

[code]DECLARE @status varchar(10)
DECLARE @pos6 int
DECLARE @pos7 int
DECLARE @states varchar(5000)
DECLARE @code_status varchar(20)
DECLARE @sql_tsk varchar(8000)

SET @status='S'
SET @states='AR,AK,CA,CO,HI,IA,ID,IN,KS,LA,MN,MO,MS,MT,ND,NE,NM,NV,OK,OR,PA,SD,TN,UT,WA,WI,WY'
SET @code_status ='L,X,Z'
SET @pos6=0
SET @pos7=13

SET @sql_tsk='SELECT b.emp_number,b.emp_name,b.code_status,b.states,a.generated_by
FROM tbl_emp_tasks a
INNER JOIN tbl_emp_detail b
ON a.emp_number=b.emp_number AND
a.status IN(' + @status + ') AND
b.code_status IN(' + @code_status + ')AND
SUBSTRING(b.emp_number,6,2)BETWEEN @pos6 AND @pos7'
Exec (@sql_tsk)[/code]

This dynamic query is giving me an error..How do I pass all my strings values to the variable and make my query running.

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-01-05 : 20:50:53

See BOL for sp_executesql, which lets you pass parameters.

However, are you sure you need dynamic SQL? Why not do something like this: http://sqlteam.com/item.asp?ItemID=11499

Cheers
-b

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-05 : 21:14:53

Change to

SUBSTRING(b.emp_number,6,2)BETWEEN ' + cast(@pos6 as varchar(10)) + ' AND ' + cast (@pos7 as varchar(10))

-----------------
[KH]

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-05 : 21:38:07

I Changed as mentioned above still the states are giving errors...How do I tackle this

DECLARE @status varchar(10)
DECLARE @pos6 int
DECLARE @pos7 int
DECLARE @states varchar(5000)
DECLARE @code_status varchar(20)
DECLARE @sql_tsk varchar(8000)

SET @status='S'
--SET @states='AR,AK,CA,CO,HI,IA,ID,IN,KS,LA,MN,MO,MS,MT,ND,NE,NM,NV,OK,OR,PA,SD,TN,UT,WA,WI,WY'
SET @code_status ='L,X,Z'
SET @pos6=0
SET @pos7=13

SET @sql_tsk='SELECT b.emp_number,b.emp_name,b.code_status,b.states,a.generated_by
FROM tbl_emp_tasks a
INNER JOIN tbl_emp_detail b
ON a.emp_number=b.emp_number AND
a.status IN(' + @status + ') AND
b.code_status IN(' + @code_status + ')AND
SUBSTRING(b.emp_number,6,2)BETWEEN ' + cast(@pos6 as varchar(10)) + ' AND ' + cast (@pos7 as varchar(10))AND
b.states IN(@states)
Exec (@sql_tsk)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-05 : 22:59:42

HINT : add a print @sql_tsk before your Exec
It will helps you to debug your queries before executing it.

Q : Why is @states mask off ?
Anyway @states should be

    set @states = '''AR'',''AK'',''CA'',....'

use two single quote to represent 1 single quote

on the syntax :

cast (@pos7 as varchar(10)) ' AND b.states IN(' + @states + ')'

-----------------
[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-06 : 01:19:49

>>set @states = '''AR'',''AK'',''CA'',....'

or use replace

set @states=replace(''''+@states,',',''',''')+''''

then execute it

Madhivanan

Failing to plan is Planning to fail

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-06 : 05:01:55

Hi

Try this:


DECLARE @status varchar(10)
DECLARE @pos6 int
DECLARE @pos7 int
DECLARE @states varchar(5000)
DECLARE @code_status varchar(20)
DECLARE @sql_tsk varchar(8000)


SET @status='S'
SET @states='(''AR'',''AK'',''CA'',''CO'',''HI'',''IA'',''ID'',''IN'',''KS'',''LA'',''MN'',''MO'',''MS'',''MT'',''ND'',
		''NE'',''NM'',''NV'',''OK'',''OR'',''PA'',''SD'',''TN'',''UT'',''WA'',''WI'',''WY'')'
SET @code_status ='''L'',''X'',''Z'''
SET @pos6=0
SET @pos7=13


SET @sql_tsk=
	'
	SELECT 	b.emp_number,
		b.emp_name,
		b.code_status,
		b.states,
		a.generated_by
	FROM tbl_emp_tasks a
	INNER JOIN tbl_emp_detail b
		ON a.emp_number=b.emp_number 
	AND a.status IN (''' + @status + ''') 
	AND b.code_status IN(' + @code_status + ')
	AND SUBSTRING(b.emp_number,6,2)BETWEEN ''' + cast(@pos6 as varchar) + ''' 	AND ''' + cast (@pos7 as varchar) + '''
	AND b.states IN' + @states + ''

Print @sql_tsk

Exec (@sql_tsk)

If you want your computer to be faster then throw it out of the window.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-06 : 15:11:11

Thanks everyone.It works...Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources