/* OR */
DBCC CHECKIDENT (invoice,NORESEED)
I have received the email from Grace on Tuesday, February 01, 2005 4:07 PM. Wow! She was sharing some interesting things related to SQL.
Thank you so much, Grace!
Do you know that ntext data type can’t do concatenation. To overcome this problem, we can use UPDATETEXT.
The following are the example of using UPDATETEXT.
DECLARE @ptrval binary(16) Declare @strComments nvarchar(4000) SET @strComments = 'comments' SELECT @ptrval = TEXTPTR(rd.Comment) FROM vyRequisitionDetails rd WHERE rd.RequisitionID = 'REQPhyuHT02012005-Delhi' UPDATETEXT vyRequisitionDetails.Comment @ptrval NULL 0 @strComments
EXEC and EXEC sp_executesql for Searching..
DECLARE @sql as nvarchar(100) DECLARE @paraDOB datetime SET @paraDOB = '1/1/1981' SET @sql=N'' SET @sql=N'SELECT * FROM EmpMast' SET @sql=@sql + ' WHERE DOB >= @paraDOB' exec sp_executesql @sql,N'@paraDOB datetime',@paraDOB
Cool.. isn’t it?
But there are one limitation of using sql_executesql. As this statement accept the NVARCHAR which has only 4000 words capacity, the SQL string muct be less than 4000 words. :(
But I got the solution to solve this problem. By using EXEC with VARCHAR variable is the solution for this problem.
Here is the example.
DECLARE @sql as varchar(100) DECLARE @paraDOB datetime SET @paraDOB = '1/1/1981' SET @sql='' SET @sql='SELECT * FROM EmpMast' SET @sql=@sql + ' WHERE DOB >= ''' + CONVERT(NVARCHAR,@paraDOB) + '''' EXEC (@sql) ---- Note: ( ) is important.
When we use sp_executesql, we can pass hidden parameter. sp_executesql’s performance is better than EXEC.
Note: some people used to pass all parameters if they are using sp_executesql statement. Actually, it is NOT necessary to pass all parameters. The parameters are only needed to be passed when the value of parameters are needed to get back.