February 11, 2010 at 12:12 am
SELECT COST_CENTER,'1900-01-01' AS VALID_FROM_DATE ,COCODE,CENTER_DESC,COST_CENTER_MGR FROM XYZ WITH(NOLOCK)
Which is retrive the proper value.
In the procedure while implimenting same thing like
DECLARE @VALID_FROM_DATE1 VARCHAR(20)
SET @VALID_FROM_DATE1 =1900-01-01;
SELECT COST_CENTER,@VALID_FROM_DATE1 AS VALID_FROM_DATE ,COCODE,CENTER_DESC,COST_CENTER_MGR FROM XYZ WITH(NOLOCK)
and EXECUTE USP_PLM_COSTCENTER_EXPORT_VALIDATOR why i am not getting output.
I am getting the error at run time
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@VALID_FROM_DATE1".
Kindly suggest how i fix this issue.
Thanks and Regards,
Sumanta Panda
February 11, 2010 at 12:16 am
I can't reproduce your error unless I execute the statements one by one. Do you execute all three of them as a single batch? By this I mean either select all three or none and then click execute (or press F5)? Your code runs fine on my machine.
PS: If you want to assign '1900-01-01' to a varchar, you should put quotes around it; if not, the calculation is performed (which yields 1898) and then the result is implicitly converted to a varchar.
However, if you want to work with dates, use the date or datetime datatype instead of varchar.
February 11, 2010 at 12:49 am
Panda can you please give some more sample code in the procedure to validate?
I tried and it just works fine.
you need to use quotes for datetime datatype
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
February 11, 2010 at 3:43 am
Dear Sir,
I should get the out put like this
00000010051900-01-010010SPLM 6800 ERR MSG247387 //Desired output
But now i am getting output(wrong output)
000000100519880010SPLM 6800 ERR MSG247387 //wrong output
The below code i have modified i.e
DECLARE @VALID_FROM_DATE1 VARCHAR(20)
SET @VALID_FROM_DATE1 = '1900-01-01'
SET @SQL_COSTCENTER='SELECT COST_CENTER,'+@VALID_FROM_DATE1+' AS VALID_FROM_DATE ,CO_CODE,COST_CENTER_DESC,COST_CENTER_MGR FROM xyz WITH(NOLOCK) '
--EXECUTE USP_PLM_COSTCENTER_EXPORT_VALIDATOR
Please note that its a constant date '1900-01-01' which will display for the records of the column type VALID_FROM_DATE
please suggest how i resolve this issue.
Thanks!!!
Sumanta Panda
February 11, 2010 at 3:49 am
February 11, 2010 at 3:52 am
Dear Sir,
While executing procedure i want the output should display like
0000001005 1900-01-01 0010 SPLM 6800 ERR MSG 247387 //Desired output
But now i am getting output(wrong output)
0000001005 1988 0010 SPLM 6800 ERR MSG 247387 //wrong output
The second column the value should be display 1900-01-01
But it is not working.
Kindly please help.
February 11, 2010 at 3:54 am
Can you show us what is there inside this? (if its not too big)
USP_PLM_COSTCENTER_EXPORT_VALIDATOR
---------------------------------------------------------------------------------
February 11, 2010 at 3:56 am
Try this
SET @SQL_COSTCENTER='SELECT COST_CENTER,'+ '''' + @VALID_FROM_DATE1+ ''''+' AS VALID_FROM_DATE ,CO_CODE,COST_CENTER_DESC,COST_CENTER_MGR FROM xyz WITH(NOLOCK) '
it should work 🙂
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
February 11, 2010 at 3:56 am
This is my procedure.Please help.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*--------------------------------------------------------------------------------
PURPOSE :
DEVELOPER :
DATE :
EXAMPLE :
EXECUTE USP_PLM_COSTCENTER_EXPORT_VALIDATOR
--------------------------------------------------------------------------------- */
ALTER PROCEDURE [dbo].[USP_PLM_COSTCENTER_EXPORT_VALIDATOR]
AS
BEGIN
SET NOCOUNT ON
/*--------------------------------------
VARIABLE DECLARATION SECTION
--------------------------------------- */
DECLARE @SQL_COSTCENTER VARCHAR(8000)
DECLARE @PROCESS_NAME VARCHAR(10)
DECLARE @FILE_NAME VARCHAR(10)
DECLARE @INBOUND_FIELD_NAME VARCHAR(20)
DECLARE @DATA_TO_EXCLUDE VARCHAR(20)
--DECLARE @VALID_FROM_DATE1 VARCHAR(20)
--SET @VALID_FROM_DATE1 = '1991-01-02'
DECLARE @VALID_FROM_DATE1 AS DATE
SET @VALID_FROM_DATE1=
DECLARE @COUNTER_COSTCENTER INT
--SET @SQL_COSTCENTER='SELECT COST_CENTER,'+@VALID_FROM_DATE1+' AS VALID_FROM_DATE ,CO_CODE,COST_CENTER_DESC,COST_CENTER_MGR FROM INBOUND_SAP_COST_CENTERS_FIN_APPR WITH(NOLOCK) '
SET @SQL_COSTCENTER='SELECT COST_CENTER,'+@VALID_FROM_DATE1+' AS VALID_FROM_DATE ,CO_CODE,COST_CENTER_DESC,COST_CENTER_MGR FROM INBOUND_SAP_COST_CENTERS_FIN_APPR WITH(NOLOCK) '
SET @COUNTER_COSTCENTER = 0
DECLARE CUR_TEST CURSOR
FOR
SELECT
DISTINCT PROCESS_NAME,
FILE_NAME,
INBOUND_FIELD_NAME,
DATA_TO_EXCLUDE
FROM
PLM_INTERFACE_EXCLUDE_DATA
GROUP BY
PROCESS_NAME,
FILE_NAME,
INBOUND_FIELD_NAME,
DATA_TO_EXCLUDE
OPEN CUR_TEST
FETCH NEXT FROM CUR_TEST INTO @PROCESS_NAME,@FILE_NAME,@INBOUND_FIELD_NAME,@DATA_TO_EXCLUDE
WHILE (@@FETCH_STATUS=0)
BEGIN
IF @PROCESS_NAME='EZX'
BEGIN
IF @FILE_NAME='CC'
BEGIN
SET @COUNTER_COSTCENTER = @COUNTER_COSTCENTER + 1
IF @COUNTER_COSTCENTER = 1
BEGIN
SET @SQL_COSTCENTER= @SQL_COSTCENTER + ' WHERE '+ @INBOUND_FIELD_NAME + ' <> ''' + @DATA_TO_EXCLUDE + ''''
END
ELSE
BEGIN
SET @SQL_COSTCENTER= @SQL_COSTCENTER + ' AND '+ @INBOUND_FIELD_NAME + ' <> ''' + @DATA_TO_EXCLUDE + ''''
END
END --- END OF EZX
END
FETCH NEXT FROM CUR_TEST INTO @PROCESS_NAME,@FILE_NAME,@INBOUND_FIELD_NAME,@DATA_TO_EXCLUDE
END
CLOSE CUR_TEST
DEALLOCATE CUR_TEST
PRINT '------------------------------------------------------------'
PRINT @SQL_COSTCENTER
PRINT '------------------------------------------------------------'
EXECUTE (@SQL_COSTCENTER)
END
February 11, 2010 at 3:59 am
Try to alter the procedure first and then excecute it
February 11, 2010 at 4:00 am
Thanks Paarthasarathy.Now it is working fine.
Thanks everyone.
Regards,
Sumanta
February 11, 2010 at 4:03 am
sk.panda (2/11/2010)
Dear Sir,While executing procedure i want the output should display like
0000001005 1900-01-01 0010 SPLM 6800 ERR MSG 247387 //Desired output
But now i am getting output(wrong output)
0000001005 1988 0010 SPLM 6800 ERR MSG 247387 //wrong output
The second column the value should be display 1900-01-01
But it is not working.
Kindly please help.
I find that hard to believe. Without the quotes you should get 1898 (and NOT 1988!) - see my earlier post for an explanation why. With the quotes it should work just fine.
February 11, 2010 at 4:04 am
Welcome Sumantha.
Have a Great Day. 🙂
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
February 11, 2010 at 4:14 am
It seems that this code has an error: SET @VALID_FROM_DATE1= (nothing)
Try:
SET @VALID_FROM_DATE1='19910102'
SET @SQL_COSTCENTER='SELECT COST_CENTER,'+CAST(@VALID_FROM_DATE1 AS NVARCHAR(50))+' AS VALID_FROM_DATE ,CO_CODE,COST_CENTER_DESC,COST_CENTER_MGR FROM INBOUND_SAP_COST_CENTERS_FIN_APPR WITH(NOLOCK) '
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply