SQL Server Performance

Stored Procedure - Concatenate

Discussion in 'Getting Started' started by elireu1, Aug 31, 2008.

  1. elireu1 New Member

    Hi,
    I'm trying to do a pretty simple concatenation in a stored procedure, but from some reason it doesn't work:
    please help!
    DROP PROC Temp;
    GO
    CREATE PROC Temp
    (
    @SearchStr nvarchar(100)
    )
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE Eli_Cursor CURSOR FOR
    Select Name1
    From [Northwind].[dbo].[Eli];
    Declare @FileName nvarchar(40);
    Declare @tmp nvarchar(40);
    SET @FileName = '';
    SET @tmp = ''
    OPEN Eli_Cursor;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    FETCH NEXT FROM Eli_Cursor INTO @FileName;
    SET @tmp = @FileName+'.jpg';
    update [Northwind].[dbo].[Eli]
    set Name3 = @tmp
    where Name1 = @FileName;
    END

    CLOSE Eli_Cursor;
    DEALLOCATE Eli_Cursor;
    END
    go


  2. satya Moderator

    Welcome to the forums.
    What kind of error you are getting, and what is the expected result for you?
  3. elireu1 New Member

    there is no error whatsoever. I just don't get the required output which is: Name3 (a field in the table) should be populated with a new value that shoud be: value of Name1+'.jpg', in this case: 'ABC.jpg'
    Name 1 and Name3 are fields in the table
    Thanks
    Eli
  4. Madhivanan Moderator

    [quote user="elireu1"]
    there is no error whatsoever. I just don't get the required output which is: Name3 (a field in the table) should be populated with a new value that shoud be: value of Name1+'.jpg', in this case: 'ABC.jpg'
    Name 1 and Name3 are fields in the table
    Thanks
    Eli
    [/quote]
    It is as simple as
    update [Northwind].[dbo].[Eli]
    set Name3 = Name1+'.jpg'
  5. elireu1 New Member

    this is exactly what I did, but from some reason it didn't work. now it's working, though. I must have missed something.
    tnx very much
    Eli
  6. preethi Member

    Glad to hear the issue is resolved. I want to take a step back. If the value of name3 is always equal to Name1 + '.jpg', Whydo you need this column name3, and update the entire table? You can always refer it as Name1+'.jpg'. Is this something you gave as a simplified example?
  7. elireu1 New Member

    it's just a simple example. the real system is way complex.
    thanks again.
    Eli
  8. Philippe Robert New Member

    I think it is a cast problem. Verify the type of each columns and variables you use.
    Try this : CAST(@variable as varchar(40)) for example.
  9. preethi Member

    Just to update those who haven;t seen yet, when you specify a variable as varchar (without giving the length) SQL Server will take the default of varchar(30).
  10. Madhivanan Moderator

  11. FrankKalis Moderator

  12. Madhivanan Moderator

    <P mce_keep="true">[quote user="FrankKalis"] <P>[quote user="Madhivanan"] <P>[quote user="preethi"] <P>Just to update those who haven;t seen yet, when you specify a variable as varchar (without giving the length) SQL Server will take the default of varchar(30). </P><P>[/quote]</P><P><A href="http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx">http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx</A></P><P>[/quote]</P><P>Shouldn't chat while posting. There wasn't a reply when I started writing my post. <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"></P><P>[/quote]</P><P>[:)]</P>
  13. preethi Member

    Thanks to Frank and Madivanan, who brought clarity to my post. This is what exactly I was intended to say; As we were discussing about CASTing, I was planning to say that if we specify varchar without specifying the length {in cast operation} it will end up to a cast of varchar(30). While writing I missed the words "In cast operation". Thank you again to both of you guys, you have taought me a good lesson on effective communication. :)
    This is the main reason I love this forum; I share what I know and I learn something what I do not know.
  14. FrankKalis Moderator

    [quote user="preethi"]
    Just to update those who haven;t seen yet, when you specify a variable as varchar (without giving the length) SQL Server will take the default of varchar(30).
    [/quote]
    Are you really sure about that? [;)]DECLARE
    @v varchar
    SELECT @v = '123456789'
    SELECT @v
    ----
    1

    (1 row(s) affected)
    This is a frequent misreading of BOL. The default is 1. Only when you use CAST or CONVERT it defaults to 30. Bit inconsistent if you ask me, but that's the way it is. [:)]

Share This Page