Stored Procedure PRoblem
-
Hello I planned to genralize a stored procedure where i can pass the table name field names as parameter to the storedprocedure along with the values and then use those information to add or update the table which is passed as parameter. My stored procedure look like this CREATE PROCEDURE Add_Edit_GSupportTables(@AddMode bit,@GenCode int,@GenName varchar(100),@GenTable varchar(50),@GenCodeField varchar(50),@GenNameField varchar(50)) as declare @AutoCode numeric; if @AddMode = 1 begin select @AutoCode = max(@GenCodeField) from @GenTable set @AutoCode = isnull(@AutoCode ,0) set @AutoCode = @AutoCode +1 Insert Into @GenTable values(@AutoCode,@GenName) end else begin update @GenTable set @GenNameField =@GenName where @GenCodeField = @GenCode end GO but it gives incorrect syntax near @GenTable How to do so that this stored procedure can be used as functoin to add / edit with regards vimal Help in need is the help indeed
-
Hello I planned to genralize a stored procedure where i can pass the table name field names as parameter to the storedprocedure along with the values and then use those information to add or update the table which is passed as parameter. My stored procedure look like this CREATE PROCEDURE Add_Edit_GSupportTables(@AddMode bit,@GenCode int,@GenName varchar(100),@GenTable varchar(50),@GenCodeField varchar(50),@GenNameField varchar(50)) as declare @AutoCode numeric; if @AddMode = 1 begin select @AutoCode = max(@GenCodeField) from @GenTable set @AutoCode = isnull(@AutoCode ,0) set @AutoCode = @AutoCode +1 Insert Into @GenTable values(@AutoCode,@GenName) end else begin update @GenTable set @GenNameField =@GenName where @GenCodeField = @GenCode end GO but it gives incorrect syntax near @GenTable How to do so that this stored procedure can be used as functoin to add / edit with regards vimal Help in need is the help indeed
I'd recommend splitting this into two procedures, add and edit. The query processor will either have to build one query plan with redundant code, or, more likely, recompile the query plan every time you swap the value of
@AddMode. As for your specific problem, you can only parameterize values. You can't parameterize schema. If you have to, you've probably designed your database incorrectly. Tables are not objects. Creating and dropping tables are very expensive operations. If you absolutely must do this, you should use string concatenation to form the schema part of the query, then usesp_executesqlto execute it supplying the parameters, e.g.:DECLARE @query varchar(2000)
SET @query = 'UPDATE ' + @GenTable +
' SET ' + @GenNameField + ' = @GenName WHERE ' +
@GenCodeField + ' = @GenCode'EXEC sp_executesql @query,
N'@GenName varchar(100), @GenCode int',
@GenName, @GenCodeStability. What an interesting concept. -- Chris Maunder
-
Hello I planned to genralize a stored procedure where i can pass the table name field names as parameter to the storedprocedure along with the values and then use those information to add or update the table which is passed as parameter. My stored procedure look like this CREATE PROCEDURE Add_Edit_GSupportTables(@AddMode bit,@GenCode int,@GenName varchar(100),@GenTable varchar(50),@GenCodeField varchar(50),@GenNameField varchar(50)) as declare @AutoCode numeric; if @AddMode = 1 begin select @AutoCode = max(@GenCodeField) from @GenTable set @AutoCode = isnull(@AutoCode ,0) set @AutoCode = @AutoCode +1 Insert Into @GenTable values(@AutoCode,@GenName) end else begin update @GenTable set @GenNameField =@GenName where @GenCodeField = @GenCode end GO but it gives incorrect syntax near @GenTable How to do so that this stored procedure can be used as functoin to add / edit with regards vimal Help in need is the help indeed
-
Hello I planned to genralize a stored procedure where i can pass the table name field names as parameter to the storedprocedure along with the values and then use those information to add or update the table which is passed as parameter. My stored procedure look like this CREATE PROCEDURE Add_Edit_GSupportTables(@AddMode bit,@GenCode int,@GenName varchar(100),@GenTable varchar(50),@GenCodeField varchar(50),@GenNameField varchar(50)) as declare @AutoCode numeric; if @AddMode = 1 begin select @AutoCode = max(@GenCodeField) from @GenTable set @AutoCode = isnull(@AutoCode ,0) set @AutoCode = @AutoCode +1 Insert Into @GenTable values(@AutoCode,@GenName) end else begin update @GenTable set @GenNameField =@GenName where @GenCodeField = @GenCode end GO but it gives incorrect syntax near @GenTable How to do so that this stored procedure can be used as functoin to add / edit with regards vimal Help in need is the help indeed
-
Thanks Mr Abbaskaya and Mr Mike I have 8 tables in my database which have the same structure and so i planned to do a general function so that i can make those 8 modules as a single functions and can use parameters to make it work. I have tried the way you have explained above but i am getting a syntax error.Could you tell me exactly how to implement that with correct syntax so that i can check it here. Thanks a lot for your help with regards vimal Help in need is the help indeed
-
Thanks Mr Abbaskaya and Mr Mike I have 8 tables in my database which have the same structure and so i planned to do a general function so that i can make those 8 modules as a single functions and can use parameters to make it work. I have tried the way you have explained above but i am getting a syntax error.Could you tell me exactly how to implement that with correct syntax so that i can check it here. Thanks a lot for your help with regards vimal Help in need is the help indeed
-
If you have 8 tables with the same structure you should put them all in one table and drop the seven other ones. That would save your problem. Wout Louwers
those 8 tables are same structured but due to simplicity and future oriented modifications and additions i am in a position to use 8 different tables. with regards vimal Help in need is the help indeed
-
those 8 tables are same structured but due to simplicity and future oriented modifications and additions i am in a position to use 8 different tables. with regards vimal Help in need is the help indeed
What do you mean with 'due to simplicity'. If the use of the 8 tables was that simple you wouldn't have to ask this question.;) due to simplicity and future oriented modifications and additions i am in a position to use 8 different tables. You sound like a manager (PHB). Not like a programmer.:-D Wout Louwers