Passing parameters into a string

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)

Passing parameters into a string

Author

Topic

supergirl_gem
Starting Member

4 Posts

Posted - 2006-03-09 : 10:42:19

CREATE PROCEDURE test_procedure
(
@testType nvarchar (50),

)

AS

DECLARE @strSql AS NVarchar(50)

SET @strSQL =''

IF @buildingType <> 'ALL Types'
SET @strSQL = 'SELECT * FROM [testDB] WHERE Type =@testType'

EXECUTE @strSql

GO

hi can anyone help me with the above i'm wanting to pass a string as a parameter into the above string so it is

select * from [testDB] WHERE Type = 'testValue'

can anyone help

thanks

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-09 : 10:52:40

SET @strSQL = 'SELECT * FROM [testDB] WHERE Type = ''' + @testType + ''''

Tahsin
Starting Member

34 Posts

Posted - 2006-03-09 : 10:53:56

What happens when you are calling the stored procedure and passing your parameter .. or is that your question?

What does the following output give you?
EXEC test_procedure 'testValue'

supergirl_gem
Starting Member

4 Posts

Posted - 2006-03-09 : 11:01:12

Thanks, I did try that but i think i must have forgotten something as i kept getting an error

It's now working :)

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-09 : 11:03:58

Try printing @strSQL to verify your statement is correct. You may also want to increase the length of @strSQL because "select * from [testdb] where type = 'testvalue'" has a length of 47.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources