July 26, 2006 at 8:56 pm
Iam having a proc which has dynamic sql inside that.
ex-
Use Northwind
CREATE PROC TEST(
@DATABASENAME VARCHAR(30),
@COUNTOUT INT OUTPUT
)
AS
BEGIN
DECLARE @v_SQL nVARCHAR(4000)
DECLARE @COUNT INT
SET @V_SQL='SELECT @COUNT=COUNT(*) FROM '+@DATABASENAME+'..Authors'
EXEC SP_EXECUTESQL @V_SQL,N'@COUNT INT OUTPUT',@COUNT OUTPUT
SET @COUNTOUT=@COUNT
RETURN @countout
END
the above sp returns the @countout param.The authors table exists in the pubs database .
Now i want to call this sp from another sp.
ex-
CREATE PROC CALL
AS
BEGIN
DECLARE @SQL NVARCHAR(100)
DECLARE @COUNTOUT INT
SET @SQL= 'EXEC TEST ''pubs'''
print @SQL
EXEC SP_EXECUTESQL @SQL,n'@COUNTOUT int output'
END
But iam unable to do this ..Can anyone help me in this ???
July 27, 2006 at 1:10 am
Use Northwind
Go
CREATE PROC TEST(
@DATABASENAME VARCHAR(30),
@COUNTOUT INT OUTPUT
)
AS
BEGIN
DECLARE @v_SQL nVARCHAR(4000)
DECLARE @COUNT INT
SET @V_SQL='SELECT @COUNT=COUNT(*) FROM '+@DATABASENAME+'..Authors'
EXEC SP_EXECUTESQL @V_SQL,N'@COUNT INT OUTPUT',@COUNT OUTPUT
SET @COUNTOUT=@COUNT
END
CREATE PROC CALL
AS
BEGIN
DECLARE @SQL NVARCHAR(100)
SET @SQL= 'DECLARE @COUNTOUT INT EXEC TEST ''pubs'',@countout OUT select @countout'
print @SQL
EXEC SP_EXECUTESQL @SQL
END
Thanks & Regards,
9989069383
Katakam.
July 27, 2006 at 3:53 am
Use Northwind
ALTER PROC TEST(
@DATABASENAME VARCHAR(30),
@COUNTOUT INT OUTPUT
)
AS
BEGIN
DECLARE @v_SQL nVARCHAR(4000)
DECLARE @COUNT INT
SET @V_SQL='SELECT @COUNT=COUNT(*) FROM '+@DATABASENAME+'..Authors'
EXEC SP_EXECUTESQL @V_SQL,N'@COUNT INT OUTPUT',@COUNT OUTPUT
SET @COUNTOUT=@COUNT
RETURN @countout
END
Alter PROC CALL
AS
BEGIN
DECLARE @SQL NVARCHAR(100)
DECLARE @COUNTOUT INT
SET @SQL= 'EXEC TEST ''pubs'',@COUNTOUT OUTPUT'
EXEC SP_EXECUTESQL @SQL,N'@COUNTOUT int output',@COUNTOUT output
select @COUNTOUT
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply