StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTrying to write an UPDATE comm in a Stored Procedure with Dynamic SQL
text
Body
copied!<p>Here goes, second post, first didn't go to well....</p> <p>I am calling an SP from a Gridview in ASP. I pass in a table name as a variable along with some other variables. I need to UPDATE my original table that the Gridview attaches to but also build a Datalist, I have used a Fetch and got it working fine. Cycle through those records and INSERT data into third table. I was told (in first post) I need to build the SQL string first then execute it. when I write it that way the second part of the insert does not work.</p> <p>Here is the code in its sliced up form because of efforts to find a succesfull structure....</p> <pre><code> @currTable varchar(100), @ID int, @short_Text varchar(250), @brief_Descrip varchar(250) = Null, @needsTranslation varchar(10) = Null, @prev_LangString varchar(250) = Null, @lang_String varchar(250) = Null, @original_lang_String varchar(250) = Null, @StringID_from_Master varchar(250), @GUID varchar(250) = Null /* */ AS SET NOCOUNT ON; DECLARE @userTable AS VARCHAR(200); SET @userTable = @currTable DECLARE @submitDate1 DATETIME; SET @submitDate1 = GETDATE() SET @prev_LangString = @original_lang_String SET @needsTranslation = 'false' DECLARE @sql varchar(max) -- Establish update to the language tabel of user and prepare to search DB for all strings that will need to be updated. BEGIN -- DECLARE @sql nvarchar(4000) SELECT @sql = ' UPDATE ' + @currTable + ' SET [lang_String] = ' + @lang_String + ' WHERE (ID = ' + @ID + ' '; EXEC sp_executesql @sql, N'@ID nvarchar(10)', @ID -- UPDATE @userTable -- SET [lang_String] = @lang_String, [date_Changed] = @submitDate1, [prev_LangString] = @prev_LangString, [needsTranslation] = @needsTranslation, [brief_Descrip] = @brief_Descrip -- WHERE (ID = @ID) END BEGIN DECLARE usedIN_DBScursor CURSOR FOR SELECT tblUniquetblStringsMaster_ID, Database_Name, dbKeyID_ofStringName FROM tblDBUsage WHERE (tblUniquetblStringsMaster_ID = @StringID_from_Master ); -- Declare the variables to store the values returned by FETCH. DECLARE @tblUniquetblStringsMaster_ID AS INT; DECLARE @dbKEYID as INT; DECLARE @dbName as varchar(100); OPEN usedIN_DBScursor; -- Perform the first fetch and store the values in variables. -- Note: The variables are in the same order as the columns -- in the SELECT statement. FETCH NEXT FROM usedIN_DBScursor INTO @tblUniquetblStringsMaster_ID, @dbName, @dbKEYID; -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- Update pending strings table with translation. BEGIN INSERT INTO tblPendingDBUpdates (stringMasterID, databaseName, databaseStringID, englishText, foreignLangText, submitDate, GUID) VALUES (@StringID_from_Master, @dbName, @dbKEYID, @short_Text, @lang_String, @submitDate1, @GUID); END -- SET @sql = '' -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM usedIN_DBScursor INTO @tblUniquetblStringsMaster_ID, @dbName, @dbKEYID; END CLOSE usedIN_DBScursor; DEALLOCATE usedIN_DBScursor; END RETURN </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload