Execute String sql
-
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'Bs_Proc_Serial' AND type = 'P') Drop Proc Bs_Proc_Serial GO Create Proc Bs_Proc_Serial @TableName Char(25) , @ColumnSearch Char(25) AS /* G e t S e r i a l I d D y n a m i c */ Declare @Ex NVarChar(500) Declare @Par NVarChar(100) Set @Par= N'@Serial bigint' Set @Ex = N'Select @Serial = Count(*) From ' + @TableName Set @Ex = @Ex + N' while (@Serial < 100000000 )' Set @Ex = @Ex + N' begin ' Set @Ex = @Ex + N' if Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= RTRIM(@Serial)) ' Set @Ex = @Ex + N' begin ' Set @Ex = @Ex + N' If (@serial < 10 ) ' Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) ' Set @Ex = @Ex + N' Break ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' If (@Serial < 100 ) ' Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) ' Set @Ex = @Ex + N' Break ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' If (@Serial < 1000) ' Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) ' Set @Ex = @Ex + N' Break ' Set @Ex = @Ex + N' End ' Set @Ex = @Ex + N' Set @Serial = @Serial + 1 ' Set @Ex = @Ex + N' End ' Set @Ex = @Ex + N' if (@serial < 10 ) ' Set @Ex = @Ex + N' Select ''00'' + Rtrim(@Serial) ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' if (@Serial < 100 ) ' Set @Ex = @Ex + N' Select ''0'' + Rtrim(@Serial) ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' Select Rtrim(@Serial) ' -- Set @Ex = N'Select Count(*) From ' -- Set @Ex = @Ex + @TableName Execute sp_executesql @Ex,@Par Go Exec Bs_Proc_Serial 'Ic_Category' ,'CategoryId' sql server msg: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '@Serial'. what's problem?
123
-
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'Bs_Proc_Serial' AND type = 'P') Drop Proc Bs_Proc_Serial GO Create Proc Bs_Proc_Serial @TableName Char(25) , @ColumnSearch Char(25) AS /* G e t S e r i a l I d D y n a m i c */ Declare @Ex NVarChar(500) Declare @Par NVarChar(100) Set @Par= N'@Serial bigint' Set @Ex = N'Select @Serial = Count(*) From ' + @TableName Set @Ex = @Ex + N' while (@Serial < 100000000 )' Set @Ex = @Ex + N' begin ' Set @Ex = @Ex + N' if Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= RTRIM(@Serial)) ' Set @Ex = @Ex + N' begin ' Set @Ex = @Ex + N' If (@serial < 10 ) ' Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) ' Set @Ex = @Ex + N' Break ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' If (@Serial < 100 ) ' Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) ' Set @Ex = @Ex + N' Break ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' If (@Serial < 1000) ' Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) ' Set @Ex = @Ex + N' Break ' Set @Ex = @Ex + N' End ' Set @Ex = @Ex + N' Set @Serial = @Serial + 1 ' Set @Ex = @Ex + N' End ' Set @Ex = @Ex + N' if (@serial < 10 ) ' Set @Ex = @Ex + N' Select ''00'' + Rtrim(@Serial) ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' if (@Serial < 100 ) ' Set @Ex = @Ex + N' Select ''0'' + Rtrim(@Serial) ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' Select Rtrim(@Serial) ' -- Set @Ex = N'Select Count(*) From ' -- Set @Ex = @Ex + @TableName Execute sp_executesql @Ex,@Par Go Exec Bs_Proc_Serial 'Ic_Category' ,'CategoryId' sql server msg: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '@Serial'. what's problem?
123
Try adding:
PRINT @Exso that you can be sure of the SQL string that you are building up. Regards Andy -
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'Bs_Proc_Serial' AND type = 'P') Drop Proc Bs_Proc_Serial GO Create Proc Bs_Proc_Serial @TableName Char(25) , @ColumnSearch Char(25) AS /* G e t S e r i a l I d D y n a m i c */ Declare @Ex NVarChar(500) Declare @Par NVarChar(100) Set @Par= N'@Serial bigint' Set @Ex = N'Select @Serial = Count(*) From ' + @TableName Set @Ex = @Ex + N' while (@Serial < 100000000 )' Set @Ex = @Ex + N' begin ' Set @Ex = @Ex + N' if Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= RTRIM(@Serial)) ' Set @Ex = @Ex + N' begin ' Set @Ex = @Ex + N' If (@serial < 10 ) ' Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) ' Set @Ex = @Ex + N' Break ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' If (@Serial < 100 ) ' Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) ' Set @Ex = @Ex + N' Break ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' If (@Serial < 1000) ' Set @Ex = @Ex + N' If Not Exists(Select * From ' + @TableName + N'Where ' + @ColumnSearch + N'= ''00''+ RTRIM(@Serial)) ' Set @Ex = @Ex + N' Break ' Set @Ex = @Ex + N' End ' Set @Ex = @Ex + N' Set @Serial = @Serial + 1 ' Set @Ex = @Ex + N' End ' Set @Ex = @Ex + N' if (@serial < 10 ) ' Set @Ex = @Ex + N' Select ''00'' + Rtrim(@Serial) ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' if (@Serial < 100 ) ' Set @Ex = @Ex + N' Select ''0'' + Rtrim(@Serial) ' Set @Ex = @Ex + N' Else ' Set @Ex = @Ex + N' Select Rtrim(@Serial) ' -- Set @Ex = N'Select Count(*) From ' -- Set @Ex = @Ex + @TableName Execute sp_executesql @Ex,@Par Go Exec Bs_Proc_Serial 'Ic_Category' ,'CategoryId' sql server msg: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '@Serial'. what's problem?
123