Return a parameter with Exec

Question

Return a parameter with Exec

perico-152473

SSC Journeyman

Points: 93
More actions
July 6, 2004 at 3:23 am

#102095

Hi
I've got a SP like this :
ALTER PROCEDURE      PROC1
  (@emailId int = NULL,
  @aboId int,
  @siteId int,
  @strEmail varchar(150) = NULL,
  @mode int,
  @NbAbo int OUTPUT)
AS
DECLARE @Where varchar(1000)
SET @Where = ' ABO_ID =' + Cast(@aboId as varchar) + ' and SITE_ID =' + Cast(@siteId as varchar)
if @mode = 1 /* insert */
  begin
   SET @Where = @Where + ' and EMAIL_LIB = ' + @strEmail
  end
else
if @mode = 2 /* update */
  begin
   SET @Where = @Where + ' and EMAIL_ID <> ' + Cast(@emailId as varchar) + ' and EMAIL_LIB = ' + @strEmail
  end
Exec('SELECT @NbAbo = Count(DLZ_EMAIL.EMAIL_ID) FROM DLZ_EMAIL WHERE ' + @Where)
But i've always @NbAbo = NULL value even if conditions are good.Why??? How can i return a parameter OUTPUT with an Exec command ?
Thx a lot

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 1

The problem is that with EXEC or sp_executesql any variables you use in your select are created, used and destroyed in the scope of the EXEC. Thus, when the EXEC starts it creates a new local variable called @NbAbo which it destroys when the EXEC finishes.

Maybe someone else on the forum has a clever solution for this, but the only way I've been able to get round this is to create a temp table, insert the count into this table and query the table after the EXEC.

You could also try experimenting with Global Variables, though I haven't seen any examples of them outside of DTS.

When in doubt - test, test, test!

Wayne

perico-152473 SSC Journeyman Points: 93 More actions · Answer 2

Ok- Thx

But have you got a sample code to do this ?

Thx again

perico-152473 SSC Journeyman Points: 93 More actions · Answer 3

K good it works ! I've found the solution :

DECLARE @Where varchar(1000)

CREATE TABLE #Temp (Nb int)

SET @Where = ' ABO_ID =' + Cast(@aboId as varchar) + ' and SITE_ID =' + Cast(@siteId as varchar)

if @mode = 1

begin

SET @Where = @Where + ' and EMAIL_LIB = ' + @strEmail

end

else

if @mode = 2

begin

SET @Where = @Where + ' and EMAIL_ID <> ' + Cast(@emailId as varchar) + ' and EMAIL_LIB = ' + @strEmail

end

INSERT INTO #Temp (Nb)

Exec('SELECT Count(DLZ_EMAIL.EMAIL_ID) FROM DLZ_EMAIL WHERE ' + @Where)

SELECT @NbAbo = Nb FROM #Temp

DROP TABLE #Temp

Thx for the idea !

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Here's an example utilizing sp_executesql:

http://www.insidesql.de/modules.php?op=modload&name=News&file=article&sid=134

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Forgot to mention that you should run this against the pubs database

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

perico-152473 SSC Journeyman Points: 93 More actions · Answer 6

Thx Frank ! Your solution is better for performance i think.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

It depends on several factors which one offers best performance. The only way to find this out is to test both in your environment. Dynamic SQL is mostly considered not a good thing. You might want to read the articles from SQL Server MVP Erland Sommarskog at http://www.sommarskog.se to find out why and if there is a workaround.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]