Dynamic SQL in SQL Server

Question

I have a SQL CLR stored procedure dbo.Parallel_AddSql that takes a SQL query to execute as a string parameter.

It looks something like this :

exec dbo.Parallel_AddSql 'sql1', 
         'insert into test.dbo.table_1 values (1, ''test1'')'

I need to pass my SQL query as dynamic SQL statement to Parallel_AddSql procedure.

SQL statement that I need to pass is procedure execution statement with parameters i.e

Exec dbo.MatchLastName @LastNameFromUser = @LastName,
 @checkbool = @checkbool

How do I pass this? As @lastName and @checkbool will be out of scope if I pass them as such in the string.

I tried using this :

set @SQL = 'Exec dbo.MatchFirstName @FirstNameFromUser =' + @firstname + ', 
@checkbool = ' + cast(@checkbool as nvarchar(10))
exec dbo.Parallel_AddSql 'sql1', @SQL

However, I get this error :

Could not find stored procedure 'dbo.MatchFirstName'

dbo.MatchFirstName is there but dbo.Parallel_AddSql is not able to see it at all.

dbo.Parallel_AddSql is coming the library code given here

Edit : Unclosed quotation mark error :

set @SQL = 'Exec dbo.MatchFirstName @FirstNameFromUser =''' + @firstname
 + ''', @checkbool = ''' + cast(@checkbool as nvarchar(10))

Yatrix · Accepted Answer · 2017-11-27 06:20:49Z

2

set @SQL = 'Exec dbo.MatchFirstName @FirstNameFromUser =''' + @firstname + ''', ...'

If you were to call the stored procure in a script, you'd write Exec dbo.MatchFirstName @FirstNameFromUser = 'John', not Exec dbo.MatchFirstName @FirstNameFromUser = John, right? Same thing with your dynamic SQL. You have to add in the quotes.

If you want to keep the @FirstName = @FirstName syntax, you'll have to declare and set your variables in the string itself. So,

'DECLARE @FirstName Varchar(Max); SET @FirstName = ''John''; EXEC ...'

EDIT

If you look at the example they gave for Parallel_AddSql, they used the fully qualified name of the procedure.

The example from www.codeproject.com:

exec ClrLibDb.dbo.Parallel_AddSql 'sql1', 'insert into test.dbo.table_1 values (1, ''test1'')'

I think it requires the database to be part of that. So, YourDbName.dbo.MatchFirstName. It may be looking at master for your stored procedure, which is why it's not finding it.

edited Nov 27, 2017 at 6:20

answered Mar 22, 2015 at 20:42

Yatrix

13.9k17 gold badges57 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Simran Over a year ago

Oh, what is that for ?

Simran Over a year ago

I get unclosed quotation mark error with what I wrote, could you please check the update and point why I am getting this error ?

Yatrix Over a year ago

@Simran You didn't close the the string after your cast statement. Append + '''' to the end of it. That should do it. Don't forget to upvote and accept the answer if I helped you out. Helpful tip: PRINT your dynamic string to the console and paste it into a script window and try to run it. That's a really easy way to see where you went wrong.

Yatrix Over a year ago

@Simran See my edit to the answer. I think that should do it.

Yatrix Over a year ago

@Simran Happy to help. My work project is currently kicking my ass, so it's nice to see someone succeed today. =)

|

Collectives™ on Stack Overflow

Dynamic SQL in SQL Server

1 Answer 1

EDIT

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related