none
Stored procedure RRS feed

  • Question

  • Hi,

    I have created a stored procedure in microsoft SQL 2008 but i get an error message when i try to execute it.

    Any Help to resolve this would be appreciated

    ------------------------------------------------------------------------------------------------------------------------

    create procedure sp_abc
                    @PARM_a varchar(16),
                    @PARM_b varchar(16),
                    @PARM_c  varchar(16)
                   
    AS
    BEGIN
            SET NOCOUNT ON;
            DECLARE @SQL as nvarchar(max)
        SET @SQL = 'SELECT  

             a,b,c,d,e,f  from (select t1.a,t2.b,t1.c,t1.d from tblABC as t1 inner join tblxyz as t2

    where t1.a=@PARM_a and t2.b=@PARM_b) as test1

    pivot

    (sum(a) for b in (e,f)) as pvt

    order by a'

    end

    --------------------------------------------------------------------------------------------------------------------------------------------------


    • Edited by dpkas Tuesday, August 9, 2011 1:03 PM
    Tuesday, August 9, 2011 12:48 PM

Answers

  • If you are using SSRS and want to PIVOT your results, try using MATRIX instead of Tablix.

    Hope, this helps !


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Wednesday, August 10, 2011 6:03 AM
  • In your code, don't use dynamic SQL, use PIVOT directly. So, to fix your current code:

    SELECT 
    
         a,b,c,d,e,f from (select t1.a,t2.b,t1.c,t1.d from tblABC as t1 inner join tblxyz as t2
    
    where t1.a=@PARM_a and t2.b=@PARM_b) as test1
    
    pivot
    
    (sum(a) for b in ([e],[f])) as pvt
    
    order by a
    

    In your code there was nothing that required dynamic SQL. 

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, August 10, 2011 2:31 PM
    Moderator

All replies

  • SQL is not enitirely portable from platform to platform.  Therefore, something that works in SQL Server will have to be rewritten to work in MySQL.
    Tom
    SQL Server MVP
    Toronto, ON Canada
    Tuesday, August 9, 2011 12:51 PM
  • Also, this is not a MySQL forum.  You need to take your MySQL questions to a MySQL forum.

    Tuesday, August 9, 2011 12:59 PM
    Moderator
  • sry,

    nt mysql,

    microsoft sql

    Tuesday, August 9, 2011 1:04 PM
  • In that case, give a look at the sp_executeSql stored procedure in books online.

    Here is an example of how you can execute a query with parameters using sp_executeSQL:

      declare @theId integer
      declare @sql nvarchar(400)
      set @theId = 4
    
      set @sql = '
      select top 500 *
      from master.dbo.sysobjects
      where Id%10 = @theId'
    
      exec sp_executeSql @sql, N'@theId int', @theId

     

    Tuesday, August 9, 2011 1:06 PM
    Moderator
  • Hi, try :

    SET @SQL = '
    SELECT a,b,c,d,e,f from (select t1.a,t2.b,t1.c,t1.d from tblABC as t1 inner join tblxyz as t2
    on t1.a='''+@PARM_a+''' and t2.b='''+@PARM_b+''') as test1
    pivot(sum(a) for b in (e,f)) as pvt 
    order by a'

     

     

    Best regards
    Tuesday, August 9, 2011 1:14 PM
  • If the above is your whole SP, then it can not give you an error as you don't do anything in it - you just declare a @SQL variable although what's the purpose of it and why did you declare it dynamic is not clear. 

    If this is not the whole SP, please post the correct code and also use SQL when insert code button, so it will be easier to read with syntax highlight.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 9, 2011 2:48 PM
    Moderator
  • Hi Naomi,

    This itself is my whole sp.

    This stored proc is called in my report as i am unable to use 'pivot ' directly in the query to design report in SSRS 2008 R2.

    The sp gets created but when i execute report using this sp as its query,i get no results instead i get an error in the report.

    I dont know what am i missing in this sp.

    after creating it as above i use 'alter' replacing 'create' keyword and add an 'EXEC @sql' as last statement.

     

     

     

    Wednesday, August 10, 2011 5:40 AM
  • Hi Badii,

    Thanks for replying.

    I used this format as well but still the error message appears.

     

    Wednesday, August 10, 2011 5:41 AM
  • If you are using SSRS and want to PIVOT your results, try using MATRIX instead of Tablix.

    Hope, this helps !


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Wednesday, August 10, 2011 6:03 AM
  • HI vinaypugalia,

    I am already using matrix.

    Wednesday, August 10, 2011 7:15 AM
  • If you are already using Matrix, then why are you looking for PIVOTing data from your tsql. Can you please explain the actual problem what you are facing.
    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Wednesday, August 10, 2011 10:43 AM
  • I actually have  a column named Month, in my table ,which i want it to be spitted according to individual months (like Jan,Feb,Mar,...,Dec).

    Thus i can see values for each month against each employee in my table.

    So i am using pivot to do the same.

    Wednesday, August 10, 2011 12:51 PM
  • In your code, don't use dynamic SQL, use PIVOT directly. So, to fix your current code:

    SELECT 
    
         a,b,c,d,e,f from (select t1.a,t2.b,t1.c,t1.d from tblABC as t1 inner join tblxyz as t2
    
    where t1.a=@PARM_a and t2.b=@PARM_b) as test1
    
    pivot
    
    (sum(a) for b in ([e],[f])) as pvt
    
    order by a
    

    In your code there was nothing that required dynamic SQL. 

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, August 10, 2011 2:31 PM
    Moderator