SP with dyn. SQL-Statements - 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

Transact-SQL (2000)

SP with dyn. SQL-Statements

Author

Topic

happymozart
Starting Member

4 Posts

Posted - 2002-11-19 : 15:01:41

Hello,

i have problems with:

@sqlWhere = ' WHERE ID=1'
EXEC ('SELECT @count=COUNT(*) FROM TABLE' + @sqlWhere)

in the main-SP @count is out of scope
are there other possiblities then
- IF ... ELSE ...
- write the whole Code in the EXEC-Statement

Thx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-19 : 15:08:58


@sqlWhere = ' WHERE ID=1' 
@count = EXEC('SELECT COUNT(*) FROM TABLE' + sqlWhere)

However, if the extent of why you need dynamic SQL is because you
wish to choose a paticular ID, I would recommend:

@ID = 1
@Count = (SELECT COUNT(*) FROM TABLE WHERE ID = @ID)

Edited by - jsmith8858 on 11/19/2002 15:51:29

happymozart
Starting Member

4 Posts

Posted - 2002-11-20 : 02:33:39

Sorry but both SQL-Statements didn't work

@sqlWhere = ' WHERE ID=1'
@count = EXEC('SELECT COUNT(*) FROM TABLE' + sqlWhere)

and

@ID = 1
@Count = (SELECT COUNT(*) FROM TABLE WHERE ID = @ID)

maybe this is the right syntax
SELECT @Count = COUNT(*) FROM TABLE WHERE ID = @ID

but i can't use this on the first example

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-20 : 13:32:04

ooops.. it needs to be:

SET @Count = (SELECT COUNT(*) FROM TABLE WHERE ID = @ID)

That should do the trick.

- Jeff

happymozart
Starting Member

4 Posts

Posted - 2002-11-21 : 02:17:08

all right

SELECT @Count = COUNT(*) FROM TABLE WHERE ID = @ID
SET @Count = (SELECT COUNT(*) FROM TABLE WHERE ID = @ID)

works, but only without EXEC

Is there a possibility for EXEC

thx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-21 : 09:07:33

I Don't think EXEC can run a value like that. You would need to use a USER-DEFINED function.

Why do you need dynamic SQL ? Can you accomidate your needs with some IF THEN logic?

- Jeff

happymozart
Starting Member

4 Posts

Posted - 2002-11-22 : 02:26:51

I have found the solution at
http://www.sqlteam.com/item.asp?ItemID=4599

***
Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.
***

there i can store the return values

thx a lot for help

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-22 : 03:02:42

you can do it without temp table

DECLARE @Cnt INT, @Sql NVARCHAR(4000), @SqlWhere NVARCHAR(4000)

SET @SqlWhere = ' WHERE ID=1'
SET @Sql = 'SET @Cnt = (SELECT COUNT(*) FROM Table' + @SqlWhere + ')'

EXEC sp_ExecuteSql @Sql, N'@Cnt INT OUT', @Cnt OUT

PRINT @Cnt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources