error in select 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)

error in select query

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-12 : 00:39:57

I am trying to run following query in SQL Server 7.0 but getting errors. Any suggestion to make it work.

Thanks,
Ujjaval.
----------

declare @tempCodeNo int
declare @tempString varchar(20)
declare @rowcounter int
declare @counter int
set @rowcounter = 0

set @counter = (select count(*) from [dbo].[codeTable])

while(@counter>0)
begin

set @query = '(select @tempCodeNo = bitmapID, @tempString = description from [dbo].[codeTable]
where bitmapID not in (select top ' + cast(@rowcounter as nvarchar)+ ' bitmapID from [dbo].[codeTable] order by bitmapID) order by bitmapID)'

exec @query

set @counter = @counter - 1
set @rowcounter = @rowcounter + 1

print @tempCodeNo
print @tempString

end
go

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 00:54:23

1. variable @query not declared
2. use exec(@query)

-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-12 : 01:04:09

It changed the error message now.actually that was for a stored procedure that I created. Here is the complete code.

-------------
use cascadeTest
go

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_dynamicDecode]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_dynamicDecode]
GO

create procedure sp_dynamicDecode
(
@codeNo int
)
AS

set nocount on

declare @outputString varchar(50)
declare @counter int
declare @rowcounter int
declare @tempCodeNo int
declare @tempString varchar(20)
declare @tempOutput varchar(50)
declare @query varchar(1000)

set @counter = (select count(*) from [dbo].[codeTable])
set @rowcounter = 0
set @outputString = ''

while(@counter > 0)
begin
set @query = '(select @tempCodeNo = bitmapID, @tempString = description from [dbo].[codeTable]
where bitmapID not in (select top ' + cast(@rowcounter as nvarchar)+ ' bitmapID from [dbo].[codeTable] order by bitmapID) order by bitmapID)'
exec (@query)
set @tempOutput = ', ' + @tempString
print @tempOutput
if (@codeNo & @tempCodeNo) = @tempCodeNo
set @outputString = @outputString + @tempOutput

set @counter = @counter - 1
set @rowcounter = @rowcounter + 1
end

set @outputString = right(@outputString, len(@outputString)-2)

select @outputString
---------------

to execute this stored procedure I am doing this:
-------------
use cascadeTest
go

sp_dynamicDecode 12
---------------

And I'm getting the following error messages:

------------------
Server: Msg 137, Level 15, State 1, Line 1949556518
Must declare the variable '@tempCodeNo'.
Server: Msg 156, Level 15, State 1, Line 1949556518
Incorrect syntax near the keyword 'order'.
Server: Msg 137, Level 15, State 1, Line 1949556518
Must declare the variable '@tempCodeNo'.
Server: Msg 156, Level 15, State 1, Line 1949556518
Incorrect syntax near the keyword 'order'.
Server: Msg 137, Level 15, State 1, Line 1949556518
Must declare the variable '@tempCodeNo'.
Server: Msg 156, Level 15, State 1, Line 1949556518
Incorrect syntax near the keyword 'order'.
Server: Msg 137, Level 15, State 1, Line 1949556518
Must declare the variable '@tempCodeNo'.
Server: Msg 156, Level 15, State 1, Line 1949556518
Incorrect syntax near the keyword 'order'.
Server: Msg 137, Level 15, State 1, Line 1949556518
Must declare the variable '@tempCodeNo'.
Server: Msg 156, Level 15, State 1, Line 1949556518
Incorrect syntax near the keyword 'order'.
Server: Msg 137, Level 15, State 1, Line 1949556518
Must declare the variable '@tempCodeNo'.
Server: Msg 156, Level 15, State 1, Line 1949556518
Incorrect syntax near the keyword 'order'.
Server: Msg 536, Level 16, State 1, Line 1949556518
Invalid length parameter passed to the substring function.

--------------------------------------------------

Any ideas now??

Thanks in advance.
Ujjaval

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 01:28:31

the variable @tempCodeNo is out of scope. Change you code to use sp_executesql. Unlike exec() it support parameters.

-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 02:28:35

Why do you need dynamic SQL?
Post your exact requirement

Madhivanan

Failing to plan is Planning to fail

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-12 : 20:18:13

I tried using sp_executesql. But it can't take more than one variable in argument i think as it gave me error. Also, I want the query output in @tempCodeNo and @tempString variable. So, that I can use it later.

I tried to break my sql into two parts, one for @tempCodeNo and the other for @tempString.

But I can't get the values in both variable. it gets printed on screen though.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 02:01:11

See if this helps
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Mubarak110
Starting Member

2 Posts

Posted - 2009-06-29 : 09:31:36

Hi
Try to include all the variable declaration within the begin and end statement.

good luck

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 10:10:39

Your requirements and business rules are not known to us.

Please read this blog post to understand WHY this is unknown to us.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

Please read this blog post HOW to provide sample data
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-29 : 10:20:48

quote:
Originally posted by Mubarak110

Hi
Try to include all the variable declaration within the begin and end statement.

good luck

This thread is three years old. I doubt OP must have solved the issue much earlier

Madhivanan

Failing to plan is Planning to fail

Mubarak110
Starting Member

2 Posts

Posted - 2009-06-30 : 15:03:46

quote:
Originally posted by madhivanan

quote:
Originally posted by Mubarak110

Hi
Try to include all the variable declaration within the begin and end statement.

good luck

This thread is three years old. I doubt OP must have solved the issue much earlier

Madhivanan

Failing to plan is Planning to fail

lol, did not even look at the date, yah the OP must have solve it by now

thanks for reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources