July 28, 2005 at 3:27 pm
I'm trying to create a Stored Procedure...
CREATE PROCEDURE [dbo].[R_WhseOrders]
@Date1 datetime
AS
--SET NOCOUNT ON
DECLARE
@sql nvarchar(4000),
@param nvarchar(25)
Select @sql = ......blaa blaaa (way way over 4000 characters)....
SELECT @param =
'@xDate1 datetime'
EXEC sp_executesql @sql, @param, @Date1
My @sql is around 8000 in size and sp_executesql can only take nvarchar which will only allow 4000 in size. Is there a work around for this??
July 28, 2005 at 3:50 pm
Nevermind.
workaround
EXEC('EXEC sp_executesql N''' + @sql1 +'',''+ @sql2 + @sql3 ''', N''' + @param +''', '''+ @Date1 +'''')
July 29, 2005 at 4:28 am
The best time for finding the answer to a question is about 10 minutes after posting it to a forum. I'm glad it's not just me!
--
Scott
July 29, 2005 at 8:24 am
SLBt
As you may know, sp_executesql implicitly converts [whatever you send to it as a statement] to NTEXT. While you can't declare a local variable as NTEXT, you -can- declare an NTEXT parameter for a stored procedure.
A simple proc to demo this is
CREATE PROCEDURE slbt_texttest @stmt NTEXT AS
EXEC sp_executesql @stmt
(Of course, you have to be careful where you place single and double quotes)
I executed the procedure through Query Analyzer and it seemed to work OK:
EXEC slbt_texttest ' '
Cheers,
Ken
July 29, 2005 at 9:03 am
If the text is around 8000 characters than you can decalre the @stmt VARCHAR(8000)
AND use EXEC(@stmt) as well.
Prasad Bhogadi
www.inforaise.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply