dynamic sql 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

SQL Server Development (2000)

dynamic sql query

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2002-05-10 : 16:00:03

pls take a look at my query:


Declare @allowAll bit
Set @sql = 'Select @allowAll = CD.blnAllowAll from CodeDpndCol2 CD inner join CodePFMulti2 CP
	on CD.lngCodeSourceID = CP.lngCodeID inner join Element' + @acctLevel + '_Codes EC
	on EC.lngIndex = CD.lngCodeDependID where CP.lngProfID = ' +  @profID + ' and EC.txtCostCenter = ' + @array_value

I have to make this dynamic because one of the tables (Element#_Codes) I'm joining is got to be dynamic. I then use the value of @allowAll in my query somewhere. When I run this query, I get the error "@allowAll must be declared" but it is declared. If I change the query to look like this:


Declare @allowAll bit
Set @sql = 'Select ' + @allowAll ' + = CD.blnAllowAll from CodeDpndCol2 CD inner join CodePFMulti2 CP
	on CD.lngCodeSourceID = CP.lngCodeID inner join Element' + @acctLevel + '_Codes EC
	on EC.lngIndex = CD.lngCodeDependID where CP.lngProfID = ' +  @profID + ' and EC.txtCostCenter = ' + @array_value

I get the error "Invalid operator for data type. Operator equals add, type equals bit."

What's the best way of writing this dynamic query? Help pls.

(I previewed this post and it seems the '+' character before and after the variables are not being shown. There are '+' chars before and after the variables.)

sherrys
Starting Member

37 Posts

Posted - 2002-05-10 : 16:12:49

You have got one of your single quotes in the wrong place.
Set @sql = 'Select ' + @allowAll ' +
should be
set @sql = 'Select ' + @allowAll ' +

PeterG
Posting Yak Master

156 Posts

Posted - 2002-05-10 : 16:17:09

i corrected the error but it still gives me the 2nd error.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-10 : 16:20:54

scope scope scope scope scope scope scope scope scope scope scope scope scope scope scope scope scope scope scope scope scope scope

The @allowALL in your outside batch is a different @allowALL that is in your @sql string . . . dynamic sql is run in as a seperate batch....

Your second attempt won't work on several levels. First, you can't use the concatination operator '+' with a bit datatype. . . .you must cast it as a char or varchar first. But even then it won't work because @allowAll is null at the 'Set' time and thus @sql will become null . . . (assuming ansi nulls on)

solution?

two of them

first


create table #allowall (blnallowall bit)
Set @sql = 'Select CD.blnAllowAll from CodeDpndCol2 CD inner join CodePFMulti2 CP
	on CD.lngCodeSourceID = CP.lngCodeID inner join Element' + @acctLevel + '_Codes EC
	on EC.lngIndex = CD.lngCodeDependID where CP.lngProfID = ' +  @profID + ' and EC.txtCostCenter = ' + @array_value 
insert #allowall
exec(@sql)

select * from #allowall

...the second is to use sp_executesql. I'll let you look that up in BOL on your own . . .

<O>

PeterG
Posting Yak Master

156 Posts

Posted - 2002-05-10 : 16:36:10

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources