How to modify these procedure for my input is null or zero

  • Hi Friends,

    i m creating web application for state and dist wise map

    i ve the tables like

    create table ind_state

    (

    ind_stat_id int,

    ind_state_name varchar(50)

    )

    insert into ind_state values ('1','Pondi')

    values ('2','TamilNadu')

    create table ind_state_dist

    (

    ind_dist_id int,

    ind_dist_name varchar(100),

    ind_stat_id int

    )

    insert into ind_state_dist values ('1','mahe','1')

    values ('2','KaraiKal','1')

    values ('3','Chennai','2')

    values ('4','Salem','2')

    create table ind_geo_loc

    (

    pincode int,

    latitude float,

    longitude float,

    ind_dist_id int

    )

    insert into ind_geo_loc values ('600004','13.0656','80.267200000000003','2')

    values ('654012','24.230','75.1462','2')

    values('609609','10.916700000000001', '79.816699999999997 ','1')

    create table ind_cust

    create table ind_cust_det

    (

    pincode int,

    cus_id varchar(50),

    sales varchar(50)

    )

    insert into ind_cust_det

    values ('600004','509', 600.0)

    values ('609609',522,400)

    my output is depends on the dist selection so made procedure

    alter procedure LAT_TEST_DYNAM0

    @dist_id int

    as

    begin

    create table #temp (pincode varchar(10) ,latitude numeric(18,10),longitude numeric(18,10) ,descp varchar(5000))

    if @dist_id!=0

    begin

    insert into #temp(pincode,latitude,longitude)

    select distinct a.pincode,b.latitude,b.longitude from

    ind_cust_det a (nolock) inner join ind_geo_loc b on a.pincode=b.pincode

    where

    b.ind_dist_id=@dist_id

    declare @pin int

    declare cur cursor dynamic for select distinct pincode from #temp

    declare @sales numeric (18,2)

    declare @total numeric (18,2)

    declare @des varchar(5000)

    declare @cust_id int

    open cur

    fetch first from cur into @pin

    while @@fetch_status=0

    begin

    set @des=''

    set @total=0

    set @des='<table border="1"><tr><th>Customer</th><th>Sales</th></tr>'

    declare cur1 cursor dynamic for select cust_id,sum(sales) sales from ind_cust_det (nolock) where pincode=@pin group by cust_id

    open cur1

    fetch first from cur1 into @cust_id,@sales

    while @@fetch_status=0

    begin

    set @des=@des+ '<tr><td align="center">' + convert(varchar,@cust_id) + '</td><td align="center">' + convert(varchar,@sales) +'</td></tr>'

    set @total=@total+@sales

    fetch next from cur1 into @cust_id,@sales

    end

    set @des=@des+ '<tr><td align="center">TOTAL</td><td>' + convert(varchar,@total)+ '</td></tr></table>'

    close cur1

    deallocate cur1

    update #temp set descp=@des where pincode=@pin

    fetch next from cur into @pin

    end

    close cur

    deallocate cur

    end

    else

    begin

    insert into #temp(pincode,latitude,longitude,descp) values ('00911','20.593684','78.962880','Amrutanjan Area Wise Sales')

    end

    select * from #temp

    drop table #temp

    end

    myself doubt is when @dist_id is null or "0" how to show default value ?

    otherwise above my code is correct ah?

    kindly do the needfull

  • I fixed your sample code and added default parameter into the sproc.

    CREATE TABLE ind_state (

    ind_stat_id INT

    ,ind_state_name VARCHAR(50)

    )

    INSERT INTO ind_state

    VALUES (

    '1'

    ,'Pondi'

    )

    ,(

    '2'

    ,'TamilNadu'

    )

    CREATE TABLE ind_state_dist (

    ind_dist_id INT

    ,ind_dist_name VARCHAR(100)

    ,ind_stat_id INT

    )

    INSERT INTO ind_state_dist

    VALUES (

    '1'

    ,'mahe'

    ,'1'

    )

    ,(

    '2'

    ,'KaraiKal'

    ,'1'

    )

    ,(

    '3'

    ,'Chennai'

    ,'2'

    )

    ,(

    '4'

    ,'Salem'

    ,'2'

    )

    CREATE TABLE ind_geo_loc (

    pincode INT

    ,latitude FLOAT

    ,longitude FLOAT

    ,ind_dist_id INT

    )

    INSERT INTO ind_geo_loc

    VALUES (

    '600004'

    ,'13.0656'

    ,'80.267200000000003'

    ,'2'

    )

    ,(

    '654012'

    ,'24.230'

    ,'75.1462'

    ,'2'

    )

    ,(

    '609609'

    ,'10.916700000000001'

    ,'79.816699999999997 '

    ,'1'

    )

    CREATE TABLE ind_cust_det (

    pincode INT

    ,cus_id VARCHAR(50)

    ,sales INT

    )

    INSERT INTO ind_cust_det

    VALUES (

    '600004'

    ,'509'

    ,600.0

    )

    ,(

    '609609'

    ,'522'

    ,400

    )

    GO

    ALTER PROCEDURE LAT_TEST_DYNAM0 @dist_id INT = 2

    AS

    BEGIN

    CREATE TABLE #temp (

    pincode VARCHAR(10)

    ,latitude NUMERIC(18, 10)

    ,longitude NUMERIC(18, 10)

    ,descp VARCHAR(5000)

    )

    IF (@dist_id IS NOT NULL)

    OR (LEN(@dist_id) > 0)

    BEGIN

    INSERT INTO #temp (

    pincode

    ,latitude

    ,longitude

    )

    SELECT DISTINCT a.pincode

    ,b.latitude

    ,b.longitude

    FROM ind_cust_det a(NOLOCK)

    INNER JOIN ind_geo_loc b ON a.pincode = b.pincode

    WHERE b.ind_dist_id = @dist_id

    DECLARE @pin INT

    DECLARE cur CURSOR DYNAMIC

    FOR

    SELECT DISTINCT pincode

    FROM #temp

    DECLARE @sales NUMERIC(18, 2)

    DECLARE @total NUMERIC(18, 2)

    DECLARE @des VARCHAR(5000)

    DECLARE @cus_id INT

    OPEN cur

    FETCH first

    FROM cur

    INTO @pin

    WHILE @@fetch_status = 0

    BEGIN

    SET @des = ''

    SET @total = 0

    SET @des = '<table border="1"><tr><th>Customer</th><th>Sales</th></tr>'

    DECLARE cur1 CURSOR DYNAMIC

    FOR

    SELECT cus_id

    ,sum(sales) sales

    FROM ind_cust_det(NOLOCK)

    WHERE pincode = @pin

    GROUP BY cus_id

    OPEN cur1

    FETCH first

    FROM cur1

    INTO @cus_id

    ,@sales

    WHILE @@fetch_status = 0

    BEGIN

    SET @des = @des + '<tr><td align="center">' + convert(VARCHAR, @cus_id) + '</td><td align="center">' + convert(VARCHAR, @sales) + '</td></tr>'

    SET @total = @total + @sales

    FETCH NEXT

    FROM cur1

    INTO @cus_id

    ,@sales

    END

    SET @des = @des + '<tr><td align="center">TOTAL</td><td>' + convert(VARCHAR, @total) + '</td></tr></table>'

    CLOSE cur1

    DEALLOCATE cur1

    UPDATE #temp

    SET descp = @des

    WHERE pincode = @pin

    FETCH NEXT

    FROM cur

    INTO @pin

    END

    CLOSE cur

    DEALLOCATE cur

    END

    ELSE

    BEGIN

    INSERT INTO #temp (

    pincode

    ,latitude

    ,longitude

    ,descp

    )

    VALUES (

    '00911'

    ,'20.593684'

    ,'78.962880'

    ,'Amrutanjan Area Wise Sales'

    )

    END

    SELECT *

    FROM #temp

    DROP TABLE #temp

    END

    Alex S
  • WOW!!! I hope your users don't mind going for lunch when they fire this thing off. You have nested cursors. Even worse is your inner cursor is recreated for every iteration of the outer cursor. :w00t::w00t::w00t::w00t::w00t:

    What are trying to accomplish here. I don't think you need a cursor at all but I am not exactly sure what the desired result is.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I didn't even notice the NOLOCK hints littered throughout that. Do you know what that hint does? Are the users ok with missing and/or duplicated data occasionally when this runs?

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

    http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • It took me a while to go through your code to figure out what it was doing.

    As stated before, you just need to add a default to your parameter. But you should do a lot more than that. As Sean said, this might take a while to run due to the nested cursors.

    Here's a version that will handle the default value with no problem.

    alter procedure LAT_TEST_DYNAM0

    @dist_id int = 0

    as

    begin

    CREATE TABLE #temp(

    pincode VARCHAR(10)

    ,latitude NUMERIC(18, 10)

    ,longitude NUMERIC(18, 10)

    ,descp VARCHAR(5000)

    )

    INSERT INTO #temp

    select CAST( g.pincode AS varchar(10)),g.latitude,g.longitude,

    '<table border="1"><tr><th>Customer</th><th>Sales</th></tr>'

    + (SELECT '<tr><td align="center">' + convert(VARCHAR(10), cust_id)

    + '</td><td align="center">' + convert(VARCHAR(18), SUM(Sales)) + '</td></tr>'

    FROM ind_cust_det i

    WHERE i.pincode = g.pincode

    GROUP BY cust_id

    FOR XML PATH(''),TYPE).value('.','varchar(max)')

    + '<tr><td align="center">TOTAL</td><td>' + convert(VARCHAR(18), SUM(sales)) + '</td></tr></table>'

    from ind_cust_det c

    inner join ind_geo_loc g on c.pincode=g.pincode

    where g.ind_dist_id = @dist_id

    GROUP BY g.pincode,g.latitude,g.longitude

    UNION ALL

    SELECT CAST('00911' AS varchar(10))

    ,'20.593684'

    ,'78.962880'

    ,'Amrutanjan Area Wise Sales'

    WHERE ISNULL(@dist_id, 0) = 0

    SELECT * FROM #temp

    end

    You might not even need the temp table, I just left it there to keep the data types you were using.

    Feel free to ask any questions that you have.

    Reference: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    PS. Please check your code before posting. It was full of errors.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

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