locked
Store Procedure issue RRS feed

  • Question

  • User1953891727 posted

    ALTER PROCEDURE [dbo].[Time_Value_Change]
    @TimeValue varchar(500)
    AS
    BEGIN
    declare @xxx Nvarchar(MAX) ='';
    declare @yyy Nvarchar(MAX) ='';
    declare @Qry NVARCHAR(MAX) = '';
    declare @Qry1 NVARCHAR(MAX) = ''
    set @Qry= 'select @x = Start_Value FROM Filter_DatePredicate where Predicate_Name = '''+@TimeValue+'''';
    set @Qry1= 'select @y = End_Value FROM Filter_DatePredicate where Predicate_Name = '''+@TimeValue+''''

    declare @xx NVARCHAR(MAX) ;
    set @xx = '';
    declare @yy NVARCHAR(MAX) ;
    set @yy = '';


    EXEC sp_executesql @Qry,N'@x Nvarchar(MAX) out', @xx out;


    set @xxx = 'select '+@xx

    Exec (@xxx)


    EXEC sp_executesql @Qry1,N'@y Nvarchar(MAX) out', @yy out;


    set @yyy= 'select '+@yy

    Exec (@yyy)


    Declare @Temp table (StartDate Nvarchar (MAX),EndDate Nvarchar (MAX))

    insert into @Temp(StartDate,EndDate) values(@xxx,@yyy)


    -- CREATE TABLE #LocalTempTable(
    --StartDate NVARCHAR(MAX),
    --EndDate NVARCHAR(MAX))

    --insert into #LocalTempTable values
    --(
    --@xxx,
    --@yyy
    --)

    select StartDate,EndDate from @Temp


    END

    i want date in date format which is storeed in table in coverted format and access in c#

    i am getting value in @xxx and @yyy but when i store it in temp table and get it from there again i am getting in converted format

    start value in table is CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),101)

    and end value is CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))),DATEADD(mm,1,getdate())),101) 

     predicate_name=Cur Mth

    Friday, January 19, 2018 5:43 AM

All replies

  • User364663285 posted

    Hi,

    Please work in the same style like the below example (of Dynamic Sql)

    DECLARE @IntVariable int;  
    DECLARE @SQLString nvarchar(500);  
    DECLARE @ParmDefinition nvarchar(500);  
    
    /* Build the SQL string one time.*/  
    SET @SQLString =  
         N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID  
           FROM AdventureWorks2012.HumanResources.Employee   
           WHERE BusinessEntityID = @BusinessEntityID';  
    SET @ParmDefinition = N'@BusinessEntityID tinyint';  
    /* Execute the string with the first parameter value. */  
    SET @IntVariable = 197;  
    EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                          @BusinessEntityID = @IntVariable;  
    /* Execute the same string with the second parameter value. */  
    SET @IntVariable = 109;  
    EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                          @BusinessEntityID = @IntVariable;  

    Saturday, January 20, 2018 1:23 PM