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

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)

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

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)

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)

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???

Now break up the textarea string and process it AHEAD OF TIME into one, two, or three separate INT parameters.

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)

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