CODEWITHSUNDEEP
sqlsql-server
Shaiwal Tripathi
Shaiwal Tripathi

Reputation: 627

How to parameterize query with special character in SQL Server?

I want to execute a query with concatenation of two columns so I have done this:

Select 
    Id, PtName + ' ('+Investigation+')' as PtName, Y, M, D, PtCode 
From 
    DiagMain

But when I am trying to parameterize this query, it's not working.

Like this:

declare @Query nvarchar(MAX)

set @Query = 'Select Id, PtName + ''( +''Investigation''+ )'' as PtName, Y, M, D, Sex, PtCode FROM DiagMain'
Exec(@Query)

What I am doing wrong here?

Upvotes: 2

Views: 204

Answers (3)

RaviKant Hudda
RaviKant Hudda

Reputation: 1042

If you need to execute above query then replace your Exec(@Query) with exec sp_executesql @Query because to run dynamic query your need to call sql inbuilt procedure. So query you need to run would be following

declare @Query nvarchar(MAX)    
set @Query = 'Select Id, PtName + ''( +''Investigation''+ )'' as PtName, Y, M, D, Sex, PtCode FROM DiagMain'
exec sp_executesql @Query

Happy Coding :-)

Upvotes: 0

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

DECLARE @Query NVARCHAR(MAX)
SET @Query ='Select  Id, PtName  + '' (''+Investigation+'')'' as PtName, Y, M, D, Sex, PtCode FROM DiagMain'
PRINT @Query
Exec(@Query)

Upvotes: 1

Nisarg Shah
Nisarg Shah

Reputation: 14541

Your single quotes were misplaced, they should surround the brackets ( & ):

set @Query = 'Select Id, PtName + ''('' +Investigation+ '')'' as PtName, Y, M, D, Sex, PtCode FROM DiagMain'

You could debug this by using the print command:

print @Query

Upvotes: 4

Related Questions