August 18, 2008 at 11:20 pm
Dear All,
I wrote below procedure and it's working fine.
But it throws error when I change datatype of @strSql to VARCHAR.
Can anyone tell me why so..?????. Why it must be of type NVARHCAR..?
cREATE Procedure [dbo].[Proc_AMS_GetSearchData]
@Type varchar(50),
@status varchar(50),
@ColumnName varchar(50),
@Criteria varchar(50)
AS
BEGIN
DECLARE @strSql nVARCHAR(500) ---- this doesn't work when I change to VARCHAR....
SET @strSql = ''
SET @strSql = 'SELECT reqId,SubjectRequest,requestDate FROM tbl_AMS_M_CapexRequest'
SET @strSql = @strSql + ' WHERE RequestType = ''' + @Type + ''''
SET @strSql = @strSql + ' AND requestStatus = ''' + @status+ ''''
SET @strSql = @strSql + ' AND ' + @ColumnName + ' LIKE '''+ @Criteria + '%'''
EXECUTE SP_EXECUTESQL @strSql
END
Regards,
Santhosh Nair.
August 18, 2008 at 11:46 pm
Hello,
According to BOL the input to sp_executesql "Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext."
So it not allowed to be be a normal VarChar.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 19, 2008 at 2:20 am
Thank you so much Marsh...
What's BOL by the way..?
Once again thanks,
Regards,
Santhosh Nair.
August 19, 2008 at 2:28 am
Hello,
BOL is short for (SQL Server) Books Online.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 19, 2008 at 6:09 am
The Books Onliine are usually part of the client install, although you can choose to not install them.
They're also available online at Microsoft:
http://msdn.microsoft.com/en-us/library/ms130214.aspx
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply