Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 25th, 2008, 08:45 AM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default Store procedure help...

Hi to all...

i have a store procedure that accepts data and
i have it declared as varchar(121)...

just that the data coming in is from a textarea from the web...

if i press enter i get and error from the DB...

is there a way i can change an enter coming in...

thanking you in advance...

Rino

 
Old June 25th, 2008, 10:08 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Nothing you've described would necessarily cause an error, are you sure it's the 'enter' character that is doing this? If the text is too long for the column then you need to restrict the textarea using client-side script and truncate the data when it arrives on the server.


--

Joe (Microsoft MVP - XML)
 
Old June 25th, 2008, 10:20 AM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default

i changed the variable to a varchar i had it has an int...
i don't get an error any more...
but if i execute my procedure with a space it returns 2 rows...
exec sp_GetAll '999997 200900'

if i execute my procedure with an enter it returns 1 row...
exec sp_GetAll '999997
200900'

how can i get it two work with either or


 
Old June 25th, 2008, 10:33 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Please read your posts before sending, do you really think anyone can answer this sort of question with the level of detail you have given?

You need to show the relevant parts of the stored procedure so that we can see how you handle the text.
What should happen with multiple entries separated by whitespace?

--

Joe (Microsoft MVP - XML)
 
Old June 25th, 2008, 10:44 AM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default

Sorry about that...
here's my SP...

When i execute it and put a space inbetween the quotes it works fine but when i enter and the text goes on the next line it only returns 1 out of 2...

CREATE PROCEDURE sp_GetAll
    @intSKU varchar(22)
AS
    declare @intSKULookUp varchar(6)
    declare @intSKULookUp1 varchar(6)
    declare @intSKULookUp2 varchar(6)

    SET @intSKULookUp = Substring (@intSKU,1,6)
    SET @intSKULookUp1 = Substring (@intSKU,8,6)
    SET @intSKULookUp2 = Substring (@intSKU,15,6)

    select MasterSKU,Category,VendorStyle,'$ ' + CONVERT( VARCHAR(8), CONVERT( MONEY, RetailPrice ), 1 ) as RetailPrice, HeaderEng1, HeaderEng2, DetailEng1, DetailEng2 from HRSTALKR
    WHERE (MasterSKU = @intSKULookUp or
        MasterSKU = @intSKULookUp1 or
        MasterSKU = @intSKULookUp2)

 
Old June 25th, 2008, 02:24 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Really really bad SP design!

Instead of passing in a single VARCHAR parameter, pass in 3 INT parameters.

Your naming also is bad. WHY would you name a variable "@intXXX" and then have it be a VARCHAR type???

Code:
CREATE PROCEDURE sp_GetAll
    @SKU1 INT default -1,
    @SKU2 INT default -1,
    @SKU3 INT default -1
AS
    select MasterSKU,Category,VendorStyle,
           '$ ' + CONVERT( VARCHAR(8), CONVERT( MONEY, RetailPrice ), 1 ) as RetailPrice, 
           HeaderEng1, HeaderEng2, DetailEng1, DetailEng2 
    from HRSTALKR 
    WHERE MasterSKU IN ( @SKU1, @SKU2, @SKU3 )
Now break up the textarea string and process it AHEAD OF TIME into one, two, or three separate INT parameters.
 
Old August 11th, 2008, 05:43 AM
Registered User
 
Join Date: Aug 2006
Location: hyderabad, Andhrapradesh, India.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi,

use this, you can send more than three parameters also. For this first you need to write one split function.


SET ANSI_NULLS OFF
GO

--**************************************
-- Name: Split Function
-- Description:Enables SQL Server to perform the Split Function in stored procedures/views/functions
-- Inputs:A STRING that you would like to split down into individual elements, based on the DELIMITER specified
-- Returns:a table with a row for each item found between the delimiter you specify
-- By Mallesh & Santosh
ALTER FUNCTION [dbo].Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
    BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)

    IF @String IS NULL RETURN

    SELECT @INDEX = 1
    WHILE @INDEX !=0
        BEGIN
         -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
         SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
         -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
         IF @INDEX !=0
          SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
         ELSE
          SELECT @SLICE = @STRING
         -- PUT THE ITEM INTO THE RESULTS SET
         INSERT INTO @Results(Items) VALUES(LTRIM(RTRIM( @SLICE)))
         -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
         SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
         -- BREAK OUT IF WE ARE DONE
         IF LEN(@STRING) = 0 BREAK
    END
    RETURN
END


now your sp will be


CREATE PROCEDURE sp_GetAll

    @vcrSKU varchar(100)

AS
    select MasterSKU,Category,VendorStyle,
           '$ ' + CONVERT( VARCHAR(8), CONVERT( MONEY, RetailPrice ), 1 ) as RetailPrice,
           HeaderEng1, HeaderEng2, DetailEng1, DetailEng2
    from HRSTALKR
    WHERE MasterSKU IN ( SELECT ITEMS FROM DBO.SPLIT(@vcrSKU,' here your DELIMITER')

Regards,
Nehru
Mumbai (India)




 
Old August 11th, 2008, 07:09 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Good idea... but WHILE loops make for some horribly slow code compared to set based methods... please see the following articles...

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

--Jeff Moden




Similar Threads
Thread Thread Starter Forum Replies Last Post
Store procedure help ??? RinoDM SQL Server 2000 8 May 1st, 2008 03:03 PM
store procedure if else problem krshekhar SQL Language 0 February 20th, 2008 05:31 AM
Create Store Procedure ?? kumiko SQL Language 7 January 4th, 2008 02:11 AM
Store Procedure sureshyuga SQL Server 2000 0 May 18th, 2007 01:49 AM
Store Procedure Problem jazzcatone ASP.NET 2.0 Basics 3 March 28th, 2006 12:41 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.