Weird SP problem - 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

General SQL Server Forums

New to SQL Server Programming

Weird SP problem

Author

Topic

BuildHome
Starting Member

20 Posts

Posted - 2006-02-17 : 22:26:57

Hello,

I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.

I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP).
SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP
but it refer it as a string and not a code so I can't run this sub-query in the SP.

Example:


CREATE PROCEDURE usp_test

@subSQL varchar(200)

AS

SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC

GO

----------------

exec usp_test 'SELECT TOP 15 id FROM tbl2'

What's wrong here? Am I working in a wrong method?

Thanks for the helpers,
BuildHome

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-17 : 22:35:29

You have to use dynamic SQL if you want to do this

declare @sql varchar(1000)
select @sql = 'SELECT id FROM tbl WHERE id IN (' + @subSQL + ') ORDER BY id DESC'
exec (@sql)

----------------------------------
'KH'
Time is always against us

BuildHome
Starting Member

20 Posts

Posted - 2006-02-17 : 22:39:10

What do you mean "dynamic SQL"? All the code that you have wrote I need to write it in my SP or SQL in my ASP page? :|

Thank you :-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-17 : 22:46:39

Dynamic SQL basically means the query is generated dynamically. Typically it involve using of exec() or sp_executesql.

Refer to here for more information.

"All the code that you have wrote I need to write it in my SP or SQL in my ASP page?"
You can do this in your stored procedure as what you have done. Just change it accordingly like :

CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
BEGIN
    declare @sql varchar(1000)
    SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
    select @sql = 'SELECT id FROM tbl WHERE id IN (' + @subSQL + ') ORDER BY id DESC'
    exec (@sql)
END

----------------------------------
'KH'
Time is always against us

BuildHome
Starting Member

20 Posts

Posted - 2006-02-17 : 22:57:00

Thank you very much! I read the article that you linked to and it's great article. I have learned something new