2

I have to create a stored procedure where I will pass tableName, columnName, id as parameters. The task is to select records from the passed table where columnName has passed id. If record is found update records with some fixed data. Also implement Transaction so that we can rollback in case of any error.

There are hundreds of table in database and each table has different schema that is why I have to pass columnName.

Don't know what is the best approach for this. I am trying select records into a temp table so that I can manipulate it as per requirement but its not working.

I am using this code:

ALTER PROCEDURE [dbo].[GetRecordsFromTable] 
    @tblName nvarchar(128),
    @keyCol varchar(100),
    @key int = 0 
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        --DROP TABLE #TempTable;

        DECLARE @sqlQuery nvarchar(4000);
        SET @sqlQuery = 'SELECT * FROM ' + @tblName + ' WHERE ' + @keyCol + ' = 2';

        PRINT @sqlQuery;

        INSERT INTO #TempTable 
           EXEC sp_executesql @sqlQuery, 
                        N'@keyCol varchar(100), @key int', @keyCol, @key;

        SELECT * FROM #TempTable;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];
    END CATCH;
END

I get an error

Invalid object name '#TempTable'

Also not sure if this is the best approach to get data and then update it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sarojanand
  • 607
  • 1
  • 11
  • 30
  • Sorry. It's a horrible approach. – shawnt00 Mar 07 '15 at 04:33
  • And you'd have to explicitly create the temp table before insert. Unfortunately you can't use the results of sp_executesql that way either. – shawnt00 Mar 07 '15 at 04:34
  • The best option is to create separate procedures for each table and forget about dynamic sql. If don't want that work, then submitting the full query directly from the client and forget about stored procedures seems a better option. – Alejandro Mar 07 '15 at 12:54
  • There r so many tables and its not feasible to create separate procedures for each of them. Besides it will be hard to maintain if table schema is changed. – sarojanand Mar 07 '15 at 15:09

2 Answers2

0

If you absolutely must make that work then I think you'll have to use a global temp table. You'll need to see if it exists before running your dynamic sql and clean up. With a fixed table name you'll run into problems with other connections. Inside the dynamic sql you'll add select * into ##temptable from .... Actually I'm not even sure why you want the temp table in the first place. Can't the dynamic sql just return the results?

On the surface it seems like a solid idea to have one generic procedure for returning data with a couple of parameters to drive it but, without a lot of explanation, it's just not the way database are designed to work.

shawnt00
  • 16,443
  • 3
  • 17
  • 22
  • As I said before there are hundreds of tables in database and it's not logical to create procedures for each of them. It will be hard to manage if table schema is changed. Only reason I want to store in temporary table is I want to select row and update other table records based on this data. – sarojanand Mar 07 '15 at 15:04
  • Why do you have hundreds of tables? My guess is that many of them should be combined into large tables and it's why you're having this problem in the first place. And why do you even need to create procedures to access them? An `update` can use subqueries, for example, so your need for temporary tables still isn't clear. – shawnt00 Mar 07 '15 at 16:51
  • These r already normalized table and they r made for different purposes like users, orders, category, shipping, employees, etc. as u can see they can't be combined. We r trying to make one procedure where we can update/delete any records from any table based on parameters passed. Of course there will be some validation check before any changes take place. That is why want to get data in temporary table. – sarojanand Mar 07 '15 at 18:23
  • Ok. You'll probably have to generate a dynamic name for it and pass that around too adding another level of complexity. Another layer to your problem is that you don't know the schema so you'll have to rely on `select...into` to get it to build the table for you within the dynamic query. Temp tables go out of scope so it will have to be a global table inside the dynamic sql but I guess you could copy it to a local temp table afterward even though that's more work and an extra slowdown. – shawnt00 Mar 07 '15 at 18:35
  • so are you saying if I change it to global table, this will work? – sarojanand Mar 07 '15 at 19:02
  • I'm saying I think you'll have to use `select...into` and global temp tables. Other than the `insert` it's fairly close. – shawnt00 Mar 08 '15 at 04:17
0

You should create the temp table.

IF OBJECT_ID('tempdb..##TempTable') IS NOT NULL
            DROP TABLE ##TempTable


CREATE TABLE ##TempTable()
Renato Reyes
  • 189
  • 1
  • 1
  • 10