Pass variable into 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

SQL Server 2000 Forums

SQL Server Development (2000)

Pass variable into Exec

Author

Topic

dgarcia59
Starting Member

7 Posts

Posted - 2005-02-01 : 12:15:16

There is bound to be a right way to do this, but I can't seem to get it to work...

CREATE PROCEDURE HtListByTPL
@guidTPL uniqueidentifier, @statuswhere varchar(128)
AS
DECLARE @selectclause varchar(1048)
SELECT @selectclause = 'SELECT Ht.guidHt, guidTrip, ht1, ht2 FROM Ht INNER JOIN HtPart on Ht.guidHt = HtPart.guidHt WHERE guidTPL = @guidTPL' + @statuswhere
EXEC(@selectclause)

The @guidTrip inside the '' results in a runtime error. What is the proper way to do this?

@guidTPL is some uniqueidentifier
@statuswhere is either "" or " AND status=0"

Thanks,
Dege

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-01 : 12:18:36

SELECT @selectclause = 'SELECT Ht.guidHt, guidTrip, ht1, ht2 FROM Ht INNER JOIN HtPart on Ht.guidHt = HtPart.guidHt WHERE guidTPL = ' + @guidTPL + ' ' + @statuswhere

i must stress that passing a whole Where into a Sproc with a variable is a very bad thing. very dangerous if security is a question.

Go with the flow & have fun! Else fight the flow

dgarcia59
Starting Member

7 Posts

Posted - 2005-02-01 : 13:47:19

I tried something similar to that and got:

Error -2147217900
Invalid operator for data type. Operator equals add, type equals uniqueidentifier.

Do I have to convert the uniqueidentifier to a varchar? Is that even possible?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-01 : 13:56:37

use
convert(varchar(36), @guidTPL)
and it will convert it to varchar.

But again. think about redesigning this.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources