Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2009
    Posts
    7

    Unanswered: Passing an import variable as an column alias in a stored proc

    Hi Guys,

    I'm trying use dynamically use an import variable when calling the stored procedure so that it aliases by column names instead of harcoding the alias name. I have to do that dynamically.

    Example

    CREATE PROCEDURE dbo.BLA
    (
    @V1 varchar(10),
    @V2 varchar(10),
    @v3 varchar(20
    )
    AS

    BEGIN

    SELECT CASE WHEN X IS NULL THEN 0 ELSE X END AS 'xxxx'
    /*Note, I want to use @V3 as the alias name instead of 'xxxx'*/
    FROM TABLENAME_A
    WHERE varA = @V1
    and varB = @V2

    END


    Any Ideas anyone will be greatly appreciated.


    Thanks

    K

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    it seems it is not a column alias .. "xxx" it is the case statement alias .

  3. #3
    Join Date
    Oct 2009
    Posts
    7
    Quote Originally Posted by mishaalsy
    it seems it is not a column alias .. "xxx" it is the case statement alias .

    Hi there,

    In that SELECT statement, I actually select from a table which I then have to pivot!

    The only way I could do that is by using a Case statement to check whether the record exists.

    Anyway when you run the query I have put inside the stored proc, the result is returned as a column!!!!

    Is there anyone who is good in sql to help here?

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    can u specify the procedure here .

    behavior of sql varies when its inside a procedure and when it is not ..

    i can quick fix it .. but have to have some thing to fix


    (incase this is not the actuall procedure)

  5. #5
    Join Date
    Oct 2009
    Posts
    7
    Quote Originally Posted by mishaalsy
    can u specify the procedure here .

    behavior of sql varies when its inside a procedure and when it is not ..

    i can quick fix it .. but have to have some thing to fix


    (incase this is not the actuall procedure)

    Thanks for trying to help. But this is an actual stored proc structure. I did not post the stored proc itself hoping that one would know what I'm trying to say. Below is the code as you requested.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[ASP_GET_WORKING_HOURS]
    (
    @V_CONTRACTCODE VARCHAR(10),
    @V_WORKTYPE VARCHAR(20),
    @V_DATEFROM VARCHAR(20),
    @V_DATETO VARCHAR(20)
    )
    AS

    BEGIN
    --DECLARE VARIABLES

    DECLARE
    @Work VARCHAR(20)


    --SELECT WORK_HRS AS @Work FROM
    --(
    SELECT CASE WHEN SUM(CAST(WORK_HRS AS FLOAT)) IS NULL THEN 0 ELSE SUM(CAST(WORK_HRS AS FLOAT))END AS WORK_HRS
    FROM
    (
    SELECT DATE, SHIFT, ID, PROJECT, CONTRACTCODE,
    MIN(CASE WHEN NAME = @V_WORKTYPE THEN VALUE END) AS WORK_HRS
    FROM SHIFTDETAIL
    WHERE CONTRACTCODE = @V_CONTRACTCODE
    AND DATE BETWEEN CAST(@V_DATEFROM AS DATETIME) AND CAST(@V_DATETO AS DATETIME)
    GROUP BY DATE, SHIFT, ID, PROJECT, CONTRACTCODE
    )A
    WHERE WORK_HRS IS NOT NULL
    --)

    END


    Let me know how you go.

    Thanks

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    Quote Originally Posted by purple^Illusion
    Hi there,

    In that SELECT statement, I actually select from a table which I then have to pivot!

    The only way I could do that is by using a Case statement to check whether the record exists.

    Anyway when you run the query I have put inside the stored proc, the result is returned as a column!!!!

    Is there anyone who is good in sql to help here?
    ofcourse when you query for a result , it will turn up as a column .

    select 1 as FIRST ;

    to pivot it i need rows to convert into column .

    select i
    from (select case when 1= 1 then 1 else 0 end as i) x


    perhaps some thing like this?

    CREATE PROCEDURE dbo.BLA
    (@V1 varchar(10),
    @V2 varchar(10),
    @v3 varchar(20
    )
    AS

    BEGIN
    declare @Query as varchar(1024)
    set @sqlquery='SELECT CASE WHEN X IS NULL THEN 0 ELSE X END AS ' @v3
    /*Note, I want to use @V3 as the alias name instead of 'xxxx'*/
    set @sqlquery=sqlquery+' '+' FROM TABLENAME_A'
    set @sqlquery=:sqlquery+' '+'WHERE varA = ' @V1
    set @sqlquery=:sqlquery+' '+'and varB = '@V2

    print @Query
    exec(@Query)

    END


    give me a min . we both posted at the same time

  7. #7
    Join Date
    Aug 2009
    Posts
    262
    wrong query .. working again
    Last edited by mishaalsy; 10-27-09 at 03:13.

  8. #8
    Join Date
    Aug 2009
    Posts
    262
    alter PROC ASP_GET_WORKING_HOURS
    (
    @V_CONTRACTCODE VARCHAR(10),
    @V_WORKTYPE VARCHAR(20),
    @V_DATEFROM VARCHAR(20),
    @V_DATETO VARCHAR(20)
    )
    AS

    BEGIN
    --DECLARE VARIABLES

    DECLARE
    @Work VARCHAR(20),
    @query varchar (200)

    --SELECT WORK_HRS AS @Work FROM
    --(
    set @query='SELECT CASE WHEN SUM(CAST(WORK_HRS AS FLOAT)) IS NULL THEN 0 ELSE SUM(CAST(WORK_HRS AS FLOAT))END AS '+@work
    set @query=@query+' FROM(
    SELECT DATE, SHIFT, ID, PROJECT, CONTRACTCODE,
    MIN(CASE WHEN NAME ='+ @V_WORKTYPE
    set @query=@query+' THEN VALUE END) AS '+ @WORK
    set @query=@query+' FROM SHIFTDETAILWHERE CONTRACTCODE = '+@V_CONTRACTCODE
    set @query=@query+' AND DATE BETWEEN CAST('+@V_DATEFROM
    set @query=@query+' AS DATETIME) AND CAST('+@V_DATETO
    set @query=@query+' AS DATETIME) GROUP BY DATE, SHIFT, ID, PROJECT, CONTRACTCODE )A WHERE ' + @work
    set @query=@query+' IS NOT NULL '
    --)

    print @Query
    exec(@query)

    END


    Command(s) completed successfully.

  9. #9
    Join Date
    Oct 2009
    Posts
    7
    Quote Originally Posted by mishaalsy
    alter PROC ASP_GET_WORKING_HOURS
    (
    @V_CONTRACTCODE VARCHAR(10),
    @V_WORKTYPE VARCHAR(20),
    @V_DATEFROM VARCHAR(20),
    @V_DATETO VARCHAR(20)
    )
    AS

    BEGIN
    --DECLARE VARIABLES

    DECLARE
    @Work VARCHAR(20),
    @query varchar (200)

    --SELECT WORK_HRS AS @Work FROM
    --(
    set @query='SELECT CASE WHEN SUM(CAST(WORK_HRS AS FLOAT)) IS NULL THEN 0 ELSE SUM(CAST(WORK_HRS AS FLOAT))END AS '+@work
    set @query=@query+' FROM(
    SELECT DATE, SHIFT, ID, PROJECT, CONTRACTCODE,
    MIN(CASE WHEN NAME ='+ @V_WORKTYPE
    set @query=@query+' THEN VALUE END) AS '+ @WORK
    set @query=@query+' FROM SHIFTDETAILWHERE CONTRACTCODE = '+@V_CONTRACTCODE
    set @query=@query+' AND DATE BETWEEN CAST('+@V_DATEFROM
    set @query=@query+' AS DATETIME) AND CAST('+@V_DATETO
    set @query=@query+' AS DATETIME) GROUP BY DATE, SHIFT, ID, PROJECT, CONTRACTCODE )A WHERE ' + @work
    set @query=@query+' IS NOT NULL '
    --)

    print @Query
    exec(@query)

    END


    Command(s) completed successfully.

    Thanks mishaalsy. This works.

  10. #10
    Join Date
    Oct 2009
    Posts
    7
    Quote Originally Posted by purple^Illusion
    Thanks mishaalsy. This works.
    Hi Mishaalsy,

    I would like to use that code you gave provided as a function in sql server, I have created a function that would return the value for one of the variable but based on my requirements now, I will need to be able to select various variables from a SELECT statement that would call the functions. I have tried implementing that but, the error message I get is "is not a valid identifier."

    I know that it is not good practice to put return statement in a procedure and the better way is to use a function that would return the value abd therefore call the funtion from a SELECT statement.

    Would you be able to help on that?

    Thanks in advance.

    K

  11. #11
    Join Date
    Oct 2009
    Posts
    7
    Sorry Mishaalsy, Let me re-phrase my words.

    Can I call the stored proc within the function?

    E.g.

    SET @Return_Val = EXEC GET_WORKING_HRS @V_CONTRACTCODE, @V_WORKTYPE, @V_DATEFROM, @V_DATETO

    RETURN @Return_Val

    I am aware that this does not work and there is nothing that can be used to call a stored proc from a function. Is there a workaround for such thing?
    Last edited by purple^Illusion; 10-27-09 at 23:32.

  12. #12
    Join Date
    Oct 2009
    Posts
    7
    Please Disregard my message,

    I found a workaround that works brilliantly!

    I converted what you had into a function that just returns the result without needing to alias the column. Then from a stored proc I called the function using the the same method u used for aliasing the column to be returned and this worked!!!

    thanks.

    K
    Last edited by purple^Illusion; 10-28-09 at 00:04.

  13. #13
    Join Date
    Aug 2009
    Posts
    262
    the dbforums went inaccessable yesterday. I am sorry i could not reply to your post efficiently .

    I feel little comfort that you have found your 'workaround'.
    my apologies again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •