execsql returning output - 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)

execsql returning output

Author

Topic

edpel
Starting Member

22 Posts

Posted - 2004-05-19 : 10:57:57

I have some code which works fine until I put it into a stored procedure, I took parts of it from an article I found here at SQL Team. Here's the code.

CREATE PROCEDURE my_sp(@Array varchar(1000),@separator char(1),@table varchar(30),@numanswered char(2), @score int output)
as

BEGIN

/**
*Declare the procedure variables
*/
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
declare @select varchar (30)
declare @myselect1 varchar(1000)
declare @myselect2 varchar(1000)
declare @where varchar (10)
declare @from varchar(40)
declare @out nvarchar(4000)
set @array = @array + @separator

/**
*Initialize the variables
*/
select @select ='int select @i='
select @myselect1=''
select @myselect2=''
select @from=' from '+ @table
select @where=' where '

/**
* Begin the loop
*/
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
select @myselect1 =@myselect1+'(case isnull('+@array_value+',0) when 9 then 0 when 0 then 0 else '+@array_value+ ' end)+'
select @myselect2 =@myselect2+'(case isnull('+@array_value+',0) when 9 then 0 when 0 then 0 else 1 end) +'

-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')

end

select @out=@select +'(('+left(@myselect1,(len(@myselect1)-1))+')/('+left(@myselect2,(len(@myselect2)-1))+'))'+@from + @where +left(@myselect2,(len(@myselect2)-1))+'>='+@numanswered

print @out
declare @i int
exec sp_executesql N'@out', N'@i int output', @i output
select @i=@score
END

I can execute this fine out of the stored procedure and it returns @score. But when I run it within the procedure

declare @score int
exec my_sp 'column1,column2',',','table1','2',@score output

I get 'Line 1: Incorrect syntax near '@out'.'

What am I doing wrong? I am sure it is something stupid I am overlooking.

Thanks,
Eddie

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-05-19 : 11:06:07

You don't need the single quotes around the @Out variable in the sp_ExecuteSQL call. Also, what do you start @Select with 'int ' - this will cause a problem as well.

Raymond

edpel
Starting Member

22 Posts

Posted - 2004-05-19 : 11:26:40

Removing the quotes worked, thanks. Yeah, that int on the select was a typo that isn't usually there.

The problem now is I can print @i and see a one but when I return @score, it is null.

edpel
Starting Member

22 Posts

Posted - 2004-05-19 : 11:31:03

nevermind...I was doing something really stupid...it works now.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources