Cannot get values for using exec(@query)

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

General SQL Server Forums

New to SQL Server Programming

Cannot get values for using exec(@query)

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-31 : 09:44:37

hello friends!!
i am getting values from front end application as 1,2

i am using it as '''1'',''2'''

i am writing stored procedure

create proc [dbo].[test_1]
@id varchar(40)
as
begin
declare @str as varchar(500)
select * from table_1 where convert(varchar,uid )in (select @id)
end

and another is

create proc [dbo].[test_1]
@id varchar(40)
as
begin
declare @str as varchar(500)
set @str = 'select * from table_1 where convert(varchar,uid )in ('+@id+')'
exec(@str)
end

my table structure is name : table_1
columns datatype
uid int
uname varchar(10)

why i am getting null values for first stored procedure and if i am using second one i am getting values in query analyser but not in front end i am using ms visual studio 2005 and getting @return_value as 0 but not actual data

is there any link where i find code source for getting data through stored procedure using exec(@querystr)

T.I.A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 09:47:41

http://www.sommarskog.se/dynamic_sql.html is one source of information regarding dynamic SQL.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-31 : 10:00:46

Try adding set nocount on in the second proc:

create proc [dbo].[test_1] 
@id varchar(40)
as
begin
set nocount on
declare @str as varchar(500)
set @str = 'select * from table_1 where convert(varchar,uid )in ('+@id+')'
exec(@str)
set nocount off
end

Harsh Athalye
India.
"Nothing is Impossible"

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-31 : 10:02:52

hi Harsh

can you tell me what does it make difference???????

thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-31 : 10:19:01

quote:
Originally posted by under2811

hi Harsh

can you tell me what does it make difference???????

thanks

After each execution of the query, SQL server emits message regarding no. of rows affected. While this is fine in QA, the front-end treats each message returned as a closed recordset. So let's say, the query is
select * from authors

at the front-end side, two recordsets are returned - one containing the authors table records and another for the message - and since the message is sent after execution of query, it will be the first recordset to be received by the front-end (LIFO). SET NOCOUNT ON suppresses such messages from being sent.

Here is what BOL says...
"Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results."

It is also better to include this statement b'coz it avoids unnecessary confirmation messages, thus reducing network traffic and boosting overall performance.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-31 : 11:59:04

Without Dynamic SQL, try

select * from table_1 where '%,'+@id+',%' like '%,'+cast(id as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-01 : 00:56:54

hi Madhivanan!!!!

got it!!!!!

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 01:55:31

Also send the parameters as "'1', '2'". Only double qoutes first and last.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-02 : 12:02:56

If id is of integer datatype, then use '1,2'

Madhivanan

Failing to plan is Planning to fail