Stored Procedure EXEC - 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

Old Forums

CLOSED - General SQL Server

Stored Procedure EXEC

Author

Topic

adurstew
Starting Member

22 Posts

Posted - 2004-03-24 : 15:40:20

Hi, im trying to figure out how to return a value in a stored procedure so the stored procedure can use it. For example...


SELECT @SqlQuery = "SELECT COUNT(*) FROM " +
@TableName + " WHERE Project_Number = '"
+ @ProjectNumber + "' AND Language = " + @Language + " And "
+ @Argument

EXEC (@SqlQuery)

I need to get the value returned by running the EXEC(@SqlQuery) command. So I can continue to use it.

Any ideas?
Thanks,
Andrew J Durstewitz

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2004-03-24 : 15:43:29

How a UDF?

"Hit me with a shovel 'cause I can't believe I dug you."

adurstew
Starting Member

22 Posts

Posted - 2004-03-24 : 15:45:09

quote:
Originally posted by JustinBigelow

How a UDF?

"Hit me with a shovel 'cause I can't believe I dug you."

Huh?

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2004-03-24 : 15:51:14

A user defined function, more specifically one returning a scalar value. See Books On Line for more info.

Justin

"Hit me with a shovel 'cause I can't believe I dug you."

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 15:52:32

You need to make your SQL statement an insert of the results to a table, and then select from that table....

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 15:53:34

quote:
Originally posted by JustinBigelow

A user defined function, more specifically one returning a scalar value. See Books On Line for more info.

Justin

"Hit me with a shovel 'cause I can't believe I dug you."

Justin, I'm guessing they want SET @local_variable = COUNT(*)

Brett

8-)

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2004-03-24 : 16:02:20

quote:
Justin, I'm guessing they want SET @local_variable = COUNT(*)

Huh, inserting the results of a count just to select it again? Seems overly complicated. Maybe its just not clicking for me, lack of sleep and all.

"Hit me with a shovel 'cause I can't believe I dug you."

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 16:11:40

Hey, I didn't say it made sense, I said it's probably what they're looking for,,,

Like:


USE Northwind
GO

CREATE TABLE myTable99(rc int)
GO

DECLARE @sql varchar(8000), @rc int

SELECT @sql = 'INSERT INTO myTable99(rc) SELECT COUNT(*) FROM Orders'
EXEC(@sql)
SELECT @rc = rc FROM myTable99

SELECT @rc
GO

DROP TABLE myTable99
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-24 : 16:11:53

you can't use dynamic SQL in a UDF ... and of course, this begs the question: WHY is the tablename dynamic? What kind of design do we have set up here .... I hope not one of those "one table per customer" type databases ....

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 16:14:27

What, you guys want us to only answer questions that make sense now?

screeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeech *

* post count put on hold

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 16:32:19

quote:
Originally posted by X002548

What, you guys want us to only answer questions that make sense now?

screeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeech *

* post count put on hold

Brett

8-)

ROTFLMAO! That was great.

Tara

adurstew
Starting Member

22 Posts

Posted - 2004-03-24 : 16:43:20

quote:
Originally posted by X002548

quote:
Originally posted by JustinBigelow

A user defined function, more specifically one returning a scalar value. See Books On Line for more info.

Justin

"Hit me with a shovel 'cause I can't believe I dug you."

Justin, I'm guessing they want SET @local_variable = COUNT(*)

Brett

8-)

Thanks, that fixed it!

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 16:50:09

quote:
Originally posted by adurstew

Thanks, that fixed it!

See...

Thanks, Tara...I'LL be here all week...don't forget your waitresses and waiters...

Oh wait...that's right, I forgot....

I'm going skiing tomorrow!

WOO HOO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources