You must Sign In to post a response.
  • Category: SQL Server

    How to execute stored procedure?


    Are you looking for information on stored procedure execution? Want to know how to execute stored procedure? Read this thread to learn more about inner join with examples.



    //when I execute this stored procedure I need to get desired result but when I execting this stored procedure I didnt get exact data by missing some rows//

    //some are executed as well and when I execute later I didnt get desired result is any problem using join of columns from tbls//

    USE [db]
    GO

    /****** Object: StoredProcedure [dbo].[spSlack_GetAllTopDownloads] Script Date: 10/07/2013 13:23:11 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    ALTER PROCEDURE [dbo].[spSlack_GetAllTopDownloads]
    (
    @languageId int,
    @categoryId int,
    @pageNumber int,
    @pageSize int
    )
    AS
    BEGIN


    DECLARE @FirstRow int, @LastRow int

    SELECT @FirstRow = (@pageNumber - 1) * @pageSize + 1,
    @LastRow = (@pageNumber - 1) * @pageSize + @pageSize

    WITH TopSongs as (
    SELECT row_number() over (order by case when @categoryId = 1 then albums.ReleaseDate else songs.SongID end DESC) as RowNumber,
    songs.SongID,songs.SongTitle,songs.SongPath,songs.Duration,songs.CategoryID,songs.ArtistDescription as SingerName,
    artists.ArtistID as SingerID,artist2.ArtistID As MusicComposerId,artist2.ArtistName As MusicComposerName,
    artist1.ArtistID As HeroID,artist1.ArtistName AS HeroName,
    albums.AlbumID,albums.AlbumName,albums.Rating,albums.CastDescription,
    albums.ThumbnailPath
    FROM tblSongs songs
    INNER JOIN tblAlbums albums ON songs.AlbumID = albums.AlbumID
    INNER JOIN tblArtists artists ON songs.PrimarySingerID = artists.ArtistID
    INNER JOIN tblArtists artist1 ON albums.Hero = artist1.ArtistID
    INNER JOIN tblArtists artist2 ON albums.MusicComposer = artist2.ArtistID
    INNER JOIN tblGenerics generic ON songs.CategoryID = generic.CategoryID
    AND songs.SongID
    IN(
    SELECT TOP 100 SongID FROM tblUserDownloads
    GROUP BY SongID
    ORDER BY COUNT(SongID) DESC
    )
    WHERE albums.LanguageID=@languageId AND
    songs.CategoryID =@categoryId
    ),
    MaxCount_CTE As(
    Select Count(*) as MaxCount From TopSongs
    )
    Select RowNumber,SongID,SongTitle,SongPath,Duration,CategoryID,SingerName,SingerID,
    MusicComposerId,MusicComposerName, HeroID,HeroName,AlbumID,AlbumName,Rating,
    CastDescription,ThumbnailPath,
    case
    when (MaxCount=@pageSize or MaxCount<@pageSize ) then 1
    when (MaxCount%@pageSize=0) then MaxCount/@pageSize
    when((MaxCount>@pageSize) and (MaxCount%@pageSize!=0))then (MaxCount/@pageSize)+1
    end as PageCount
    From TopSongs, MaxCount_CTE
    Where RowNumber Between @FirstRow and @LastRow
    Order By RowNumber ASC


    END






    GO
  • #729978
    Hi,

    You didn't clearly specified what is your desired result and what actually you are getting.

    Syntactically your procedure looks ok, but it will be difficult to find issue in your sp without knowing actual result.

    You can do one thing, instead of executing complete SP together. Try to execute the part by part

    1. First try to execute by joining only two tables
    2. Later add one more join and try
    3. and then one more inner join, like this if you try you might find where the issue is and where the inner join is exactly failing.

  • #729999
    Hi,

    That is totally depending upon your table structure and your table data and relation between those tables only. For this we can't help you if you need some suggestion out of this then post sample output and expected output.


    Hope you understand..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #730005
    Hai Abhinav,
    When you execute the stored procedure, if all the syntax, table names, column names are correct then It will execute and show the message that command executed successfully.
    To get the desired rows, your stored procedure should have correct joins.
    So if you are not getting the correct results, you probably need to look for your queries.
    Just get the queries in to separate window and try to generate the required results and then you can place it in to the stored procedure.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #730037
    Hi Abi,

    Your SP's Syntax is correct..
    If you couldnt get your desired result for sometimes means problem is neither you put inner join instead of left or right joins orelse it filtered by your where conditions
    so check one by one joins and conditions you will solve this issue by yourself...

    All the best.....


    Regards,
    Sri

  • #730245
    Hi Abi,


    Check out Tables and joins..




    Regards
    Sriram.R

  • #730435
    Stored Procedure execution is performed by:

    "exec stored procedure name" this command is used to execute the stored procedure.

  • #734162
    If you want to run stored procedure then use statement

    EXEC [spSlack_GetAllTopDownloads] 'Parametername1','ParameterName2'



    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!

  • #735451
    exec [spSlack_GetAllTopDownloads] 'parameter1',parameter2','parameter3'


    regards
    sriram.r


  • Sign In to post your comments