Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    25

    Unanswered: How to concatenate string with variable

    I have sproc that I would like to execute from another sproc. The sproc I want to call takes a varchar as inparameter.
    I keep getting "Incorrect syntax near +" when I try to compile the code below. I am using mssql 2000 sp4.
    Any ideas how I can concatenate the variable @group_name with a text and send to the sproc pro_log? :

    Code:
    ALTER  proc [pro_add_group]
    @group_name nvarchar(255),@cre_date datetime=null,@del_date datetime=null,@del_flg tinyint=0
    as
    
    if @cre_date is null
    set @cre_date = getdate()
    
    if not exists
    	(select 1 from t_user_groups where group_name = @group_name)
    	begin
    	insert into t_user_groups
    	select @group_name,@cre_date,@del_date,@del_flg
    	
    	exec pro_log 'New group '+ @group_name +'created.'
    
    end
    else
    select 'Group already exists'
    return 0

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by steohl View Post
    I have sproc that I would like to execute from another sproc. The sproc I want to call takes a varchar as inparameter.
    I keep getting "Incorrect syntax near +" when I try to compile the code below. I am using mssql 2000 sp4.
    Any ideas how I can concatenate the variable @group_name with a text and send to the sproc pro_log? :

    Code:
    ALTER  proc [pro_add_group]
    @group_name nvarchar(255),@cre_date datetime=null,@del_date datetime=null,@del_flg tinyint=0
    as
    
    if @cre_date is null
    set @cre_date = getdate()
    
    if not exists
    	(select 1 from t_user_groups where group_name = @group_name)
    	begin
    	insert into t_user_groups
    	select @group_name,@cre_date,@del_date,@del_flg
    	
    	exec pro_log 'New group '+ @group_name +'created.'
    
    end
    else
    select 'Group already exists'
    return 0
    Without writing the code for you, I suggest using dynamic sql for this situation. Might workie for you.

    Code:
    declare @strSql nvarchar(2000)
    
    set @strSql = 'select * from ' + @table + ' where ' + @column + ' = "' + @pk + '" order by 1'
    exec sp_executesql @strSql

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    ALTER  proc [pro_add_group]
    	@group_name nvarchar(255),
    	@cre_date datetime=null,
    	@del_date datetime=null,
    	@del_flg tinyint=0
    as
    BEGIN
    	DECLARE @GroupName	NVARCHAR(255)
    
    	if @cre_date is null
    		set @cre_date = getdate()
    	
    	if not exists (select 1 from t_user_groups where group_name = @group_name)
    	begin
    		insert into t_user_groups
    		select @group_name,@cre_date,@del_date,@del_flg
    		
    		SET @GroupName = 'New group ' + @group_name + ' created.'
    		exec pro_log @GroupName
    	end
    	else
    		select 'Group already exists'
    
    	return 0
    END
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2005
    Posts
    25
    Quote Originally Posted by Wim View Post
    Try:
    Code:
    ALTER  proc [pro_add_group]
    	@group_name nvarchar(255),
    	@cre_date datetime=null,
    	@del_date datetime=null,
    	@del_flg tinyint=0
    as
    BEGIN
    	DECLARE @GroupName	NVARCHAR(255)
    
    	if @cre_date is null
    		set @cre_date = getdate()
    	
    	if not exists (select 1 from t_user_groups where group_name = @group_name)
    	begin
    		insert into t_user_groups
    		select @group_name,@cre_date,@del_date,@del_flg
    		
    		SET @GroupName = 'New group ' + @group_name + ' created.'
    		exec pro_log @GroupName
    	end
    	else
    		select 'Group already exists'
    
    	return 0
    END

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •