SP error

  • I have the following proc which is used in a report. Of these @rid is multi-valued. but in the report i am getting conversion from nvarchar to int error.

    Could any one help?

    CREATE PROC sp_UserPerformance (@rid bigint,@fromdate datetime,@todate datetime,@locid bigint)

    AS

    BEGIN

    Select UserName as 'User Name'

    ,isnull(( Select Sum(HC_REQ_TEAM.Position)

    From HC_REQ_TEAM WITH(NOLOCK)

    WHERE HC_REQ_TEAM.TeamUserID=HC_USERS.RID

    AND HC_REQ_TEAM.ReqID in(Select RID

    From HC_REQUISITIONS WITH(NOLOCK)

    WHERE HC_REQUISITIONS.ClientID in (16,15,13,14 ))

    and HC_REQ_TEAM.ReqID in(Select ReqID

    From HC_REQ_RESUME,HC_REQ_RESUME_STATUS WITH(NOLOCK)

    WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate)

    and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID ) ),'') as Openings

    From HC_USERS WITH(NOLOCK)

    WHERE HC_USERS.RID in (@rid)

    AND HC_USERS.Locationid in (@locid)

    Group By HC_USERS.RID,HC_USERS.UserName

    END

  • hi,

    The @rid is taking the multi values by comma seperated....

    ex: 12,45,24,3,78 ..

    So the datatype int only takes single value of int only...it doesnot allow the above value...

    For that change the @rid datatype to NVACHAR(<length/MAX>) or VARCHAR(<length/MAX>) in Ur SP.

    Use the select stmt as...

    Select rid,name,col2,col3 from tblename where rid in (Select fieldvalue from getorder(@rid))

    getOrder table function code is availabale in my posts for Sort order....

    look into that u should get clear picture that how to solve this issue...

    For multi value selection u should use varchar/nvarchar datatype in the backend..

    :-):-)

    Sasidhar Chowdary

  • hi,

    Look into the following link for the function mentioned above

    http://www.sqlservercentral.com/Forums/Topic900359-338-1.aspx#bm900398

  • Hi Shashidhar,

    Thanks for the reply. I tried it. I changed @rid to varchar in both SP and my report. but still i am getting the same error.

    if i run the query alone it works fine. but as SP, it is cusing prob.

    so can you suggest me something? also, i didnt get your getorder() function.

    Thanks,

    Malavika

  • hi,

    Its worked for me......

    I dint get any error........

    Execute the following code as it is....

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE function [dbo].[Test_GetOrderTable] (@string varchar(max))

    returns @tblOrder Table(id int identity(1,1),strings varchar(100))

    AS

    begin

    declare @LocalVar varchar(100)

    declare @pos int

    set @pos=1

    set @LocalVar=''

    set @string = ltrim(rtrim(@string))

    while (@pos <=len(@string))

    begin

    if (substring(@string, @pos,1)!=',')

    begin

    set @LocalVar =@LocalVar + substring(@string,@pos,1)

    set @pos=@pos+1

    end

    else

    begin

    if @LocalVar <> ''

    insert into @tblOrder values(ltrim(rtrim(@LocalVar)))

    set @pos= @pos+ 1

    set @LocalVar=''

    end

    end

    if @LocalVar <> ''

    insert into @tblOrder values(ltrim(rtrim(@LocalVar)))

    return

    end

    GO

    Run the following SP as it is....now test it...it should work.....

    CREATE PROC sp_UserPerformance (@rid VACHAR(MAX),@fromdate datetime,@todate datetime,@locid bigint)

    AS

    BEGIN

    Select UserName as 'User Name'

    ,isnull(( Select Sum(HC_REQ_TEAM.Position)

    From HC_REQ_TEAM WITH(NOLOCK)

    WHERE HC_REQ_TEAM.TeamUserID=HC_USERS.RID

    AND HC_REQ_TEAM.ReqID in(Select RID

    From HC_REQUISITIONS WITH(NOLOCK)

    WHERE HC_REQUISITIONS.ClientID in (16,15,13,14 ))

    and HC_REQ_TEAM.ReqID in(Select ReqID

    From HC_REQ_RESUME,HC_REQ_RESUME_STATUS WITH(NOLOCK)

    WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate)

    and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID ) ),'') as Openings

    From HC_USERS WITH(NOLOCK)

    WHERE HC_USERS.RID in (SELECT Strings from Test_GetOrderTable(@rid))

    AND HC_USERS.Locationid in (@locid)

    Group By HC_USERS.RID,HC_USERS.UserName

    END

    Cheers!

    Sasidhar Chowdary

  • Sasidhar Pulivarthi (4/15/2010)


    hi,

    The @rid is taking the multi values by comma seperated....

    ex: 12,45,24,3,78 ..

    So the datatype int only takes single value of int only...it doesnot allow the above value...

    For that change the @rid datatype to NVACHAR(<length/MAX>) or VARCHAR(<length/MAX>) in Ur SP.

    I agree with what Sasidhar says about the need to change the data type to support a comma-delimited list.

    But, please, don't use the code he posted for splitting the list. It is not set-based, and will perform very poorly when you hit it with a large record set.

    See the following highly-performing, set-based example for how to split a delimited string apart into a table that can then be used to join into a table:

    First, make a function that can be used.

    CREATE FUNCTION dbo.DelimitedSplit8K

    /***************************************************************************************************

    Purpose:

    Split a given string at a given delimiter and return a list of the split elements (items).

    Usage Example:

    SELECT *

    FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter)

    Returns:

    iTVF containing the following:

    ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)

    Item = Element value as a VARCHAR(8000)

    Notes:

    1. Optimized for VARCHAR(8000) or less.

    2. Optimized for single character delimiter.

    3. Optimized for use with CROSS APPLY.

    4. Does not "trim" elements just in case leading or trailing blanks are intended.

    5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a

    bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and

    compactness. Extra speed realized (cuts out 9 SELECT/UNION ALL's) with UNPIVOT thanks to

    Gianluca Sartori.

    6. If you don't know how a Tally table can be used to replace loops, please see the following...

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    Revision History:

    Rev 00 - 20 Jan 2010 - Jeff Moden

    - Base 10 redaction for CTE.

    Rev 01 - 08 Mar 2010 - Jeff Moden

    - Changed UNION ALL to UNPIVOT for bit of extra speed.

    Rev 02 - 13 Mar 2010 - Jeff Moden

    - Removed one additional concatenation and one subtraction from the SUBSTRING in the

    SELECT List.

    ***************************************************************************************************/

    --===== Define I/O parameters

    (

    @pString VARCHAR(8000),

    @pDelimiter CHAR(1)

    )

    RETURNS TABLE

    AS

    RETURN

    WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).

    E1(N) AS ( --=== Create Ten 1's very quickly

    SELECT N

    FROM (SELECT 1 N0, 1 N1, 1 N2, 1 N3, 1 N4, 1 N5, 1 N6, 1 N7, 1 N8, 1 N9) AS E0

    UNPIVOT (N FOR Nx IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt

    ), --10

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

    cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)

    --===== Do the split

    SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,

    SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item

    FROM cteTally

    WHERE N < LEN(@pString) + 2

    AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter

    ;

    Now, create a temp table with a delimited list, and with it call the function to split the list.

    CREATE TABLE #List (List varchar(100))

    INSERT INTO #List VALUES ('AB,CD,EF,GH,IJ,KL,MN,OP,QR,ST,UV,WZ,YZ')

    DECLARE @Delimiter char(1);

    set @Delimiter = ',';

    select f.*

    FROM #List l

    CROSS APPLY DelimitedSplit8K(l.List, @Delimiter) f

    See how the DelimitedSplit8K function returns a table that can be joined to (well, actually CROSS APPLYed to)? In Sasidhar's example, change:

    From HC_USERS WITH(NOLOCK)

    WHERE HC_USERS.RID in (SELECT Strings from Test_GetOrderTable(@rid))

    To:

    From HC_USERS

    CROSS JOIN DelimitedSplit8K(@rid, ',')

    Finally, clean up.

    DROP FUNCTION dbo.DelimitedSplit8K

    One last bit of advice... drop those "WITH (NOLOCK)" hints.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thanks Wayne. I am actually running the rep thru query but will soon change it to SP based on your hints.

    And abt that WITH(NO LOCK), i am not sure why it is used. i am new here and in each and every query they have written it.

    can you explain what is it and wht are its merits and de-merits?

    And I have one more prob now:

    I have a report which calls another report for details.

    (like i have given the "jump to report" option.

    Now i am not getting how to match the parameters between the reports.

    In my Recruitment report, we will display the total vacancies in a particular co.

    in the detail report, we will display the requirement title, no. of posts open, exp reqd and so on.

    Now company id of my master report must match with that of detail. how to do?

  • Malavika, NO LOCK is a TABLE HINT which instructs Query Optimizer (QO) to do a thing in a different way from the way QO actually planned to do..

    You can learn more about TABLE HINTS by clicking this TABLE HINTS

    Cheers!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply