Passing parameters in a SQL stored procedure 1

[scriptscribe]

Hi,

I'm trying to pass a parameter in a Stored Procedure that has single quotes in it and it's kicking back an error.

Here's my procedure:

CREATE PROC [dbo].[uspDSHReport]
	@MedicaidCACClause varchar(200)
AS
BEGIN

Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * INTO #KnownMedicaidNoPartB FROM #NoPartB '
Select @SQL = @SQL + 'WHERE (' + @MedicaidCACClause + ')'
Exec ( @SQL)
End

and the procedure call:

uspDSHReport FinClassCurrent IN ('K','W','WF','WG','WH')

I used to take this code and manually insert it into the query, as it changes for each facility that I run my report on. Now I want to create a stored procedure that will do this automatically, but because of the single quotes, it won't run.

Thanks,
Mike

 

[AlexCuse]

if you want to show a single quote in your string, you need to type it twice. Say you wanted to pass this word:

Alex's

You would pass it as 'Alex''s'

You can also concatenate char(39), passing it like this:

'Alex' + Char(39) + 's'

However, what I recommend is that you read this FAQ's: faq183-5207

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom