June 27, 2012 at 3:32 am
Hi,
I have this stored procedure:
USE [SGSC]
GO
/****** Object: StoredProcedure [dbo].[spSGCT_UPDATESINCRONISMO_SGCTLOCAIS] Script Date: 06/27/2012 10:26:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSGCT_UPDATESINCRONISMO_SGCTLOCAIS]
(@DATA1 AS varchar(50),@DATA2 AS varchar(50),@NOMEFICHEIRO AS VARCHAR(500),
@CODRF AS VARCHAR(5),@INICIO AS VARCHAR(10),@FIM AS BIGINT, @DB AS VARCHAR(50),
@CLASSIFICACAO AS INT)
AS
BEGIN
DECLARE @USER AS INT,
@STRSQL AS NVARCHAR(4000),
@PARMDEFINITION AS NVARCHAR (4000)
SET @STRSQL = 'select @val = EXEC dbo.sp_SGCT_USERSGSC '''+@DB+''''
SET @PARMDEFINITION = '@Val INT OUTPUT'
EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @USER OUTPUT
EXEC('INSERT INTO '+@DB+'.DBO.SINCRONISMO VALUES
(
'''+@DATA1+''','''+@DATA2+''' ,''I'',
'+@CLASSIFICACAO+','+@user+',NULL,'''+@NOMEFICHEIRO+''',
'''+@CODRF+''','''+@INICIO+''', '''+@FIM+''',0
)')
END
When I execute it like this:
EXEC dbo.spSGCT_UPDATESINCRONISMO_SGCTLOCAIS @DATA1,
'2012-06-27 10:28:15.760',
'PastaExportacao SGCTlocal - 4.01 - 20120523 112924 - D - 1119_CS.rar',
'4.01',
'1119',
1119,
'SGCTCentral',
1
I receive the following error message:
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@DATA1".
But @Data1 is a parameter.
Can someone help?
Thanks
June 27, 2012 at 3:41 am
I think the syntax is wrong.
Try calling your sp like this:
EXEC dbo.uspGetEmployeeManagers @EmployeeID = 6;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 27, 2012 at 3:43 am
the declaration you have made for@Data1 is only relevant to the internal part of the procedure. You are attempting to pass a parameter @Data1 from outside the procedure and would use
DECLARE @DATA1 varchar(50)
SET @DATA1 = 'somestringvalue'
EXEC dbo.spSGCT_UPDATESINCRONISMO_SGCTLOCAIS @DATA1,
'2012-06-27 10:28:15.760',
'PastaExportacao SGCTlocal - 4.01 - 20120523 112924 - D - 1119_CS.rar',
'4.01',
'1119',
1119,
'SGCTCentral',
1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 27, 2012 at 3:51 am
Yes pass some value for @Date1, it will solve your issue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply