I keep all my functions in a text file with `&#39;CREATE OR REPLACE FUNCTION somefunction&#39;`.  
So if I add or change some function I just feed the file to psql.

Now if I add or remove parameters to an existing function, it creates an overload with the same name and to delete the original I need type in all the parameter types in the exact order which is kind of tedious.

Is there some kind of wildcard I can use to `DROP` all functions with a given name so I can just add `DROP FUNCTION` lines to the top of my file?

DROP FUNCTION without knowing the number/type of parameters?

Why would someone use `WHERE 1=1 AND &lt;conditions&gt;` in a SQL clause (Either SQL obtained through concatenated strings, either view definition)

I&#39;ve seen somewhere that this would be used to protect against SQL Injection, but it seems very weird.

If there is injection `WHERE 1 = 1 AND injected OR 1=1` would have the same result as `injected OR 1=1`.

Later edit: What about the usage in a view definition?

---

Thank you for your answers.

Still,
I don&#39;t understand why would someone use this construction for defining a view, or use it inside a stored procedure.

Take this for example:

    CREATE VIEW vTest AS
    SELECT FROM Table WHERE 1=1 AND table.Field=Value

Why would someone use WHERE 1=1 AND &lt;conditions&gt; in a SQL clause?

I have a dynamic SQL statement I&#39;ve created in a stored procedure. I need to iterate over the results using a cursor. I&#39;m having a hard time figuring out the right syntax. Here&#39;s what I&#39;m doing.

    SELECT @SQLStatement = &#39;SELECT userId FROM users&#39;

    DECLARE @UserId

    DECLARE users_cursor CURSOR FOR
    EXECUTE @SQLStatment --Fails here. Doesn&#39;t like this

    OPEN users_cursor
    FETCH NEXT FROM users_cursor
    INTO @UserId

    WHILE @@FETCH_STATUS = 0
    BEGIN

    EXEC asp_DoSomethingStoredProc @UserId

    END
    CLOSE users_cursor
    DEALLOCATE users_cursor

What&#39;s the right way to do this?


Using a cursor with dynamic SQL in a stored procedure

The code is as follows: 


    ALTER PROCEDURE dbo.pdpd_DynamicCall 
    @SQLString varchar(4096) = null
    
    AS
    
    Begin
    
        create TABLE #T1 ( column_1 varchar(10) , column_2 varchar(100) )
    
        insert into #T1 
            execute (&#39;execute &#39; + @SQLString )
    
        select * from #T1 
    
    End


The problem is that I want to call different procedures that can give back different columns.
Therefore I would have to define the table #T1 generically.
But I don&#39;t know how.

Can anyone help me on this problem?


Dynamic SQL results into temp table in SQL Stored procedure

I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable.

I know I can use `sp_executesql` but can&#39;t find clear examples around about how to do this.
 

How to get sp_executesql result into a variable?

I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL?

At the moment I&#39;ve managed to come up with a SQL statement that returns all the commands I need to execute: 

    SELECT &#39;TRUNCATE TABLE &#39; ||  tablename || &#39;;&#39; FROM pg_tables WHERE tableowner=&#39;MYUSER&#39;;

But I can&#39;t see a way to execute them programmatically once I have them.

Truncating all tables in a Postgres database

How do I write an SQL script to create a ROLE in PostgreSQL 9.1, but without raising an error if it already exists?

The current script simply has:

    CREATE ROLE my_user LOGIN PASSWORD &#39;my_password&#39;;

This fails if the user already exists. I&#39;d like something like:

    IF NOT EXISTS (SELECT * FROM pg_user WHERE username = &#39;my_user&#39;)
    BEGIN
        CREATE ROLE my_user LOGIN PASSWORD &#39;my_password&#39;;
    END;

... but that doesn&#39;t work - `IF` doesn&#39;t seem to be supported in plain SQL.

I have a batch file that creates a PostgreSQL 9.1 database, role and a few other things. It calls psql.exe, passing in the name of an SQL script to run. So far all these scripts are plain SQL and I&#39;d like to avoid PL/pgSQL and such, if possible.

Create PostgreSQL ROLE (user) if it doesn&#39;t exist

How can I drop all tables whose names begin with a given string? 

I think this can be done with some dynamic SQL and the `INFORMATION_SCHEMA` tables.

Drop all tables whose names begin with a certain string

I have two tables:

    A [ID, column1, column2, column3]
    B [ID, column1, column2, column3, column4]

`A` will always be subset of `B` (meaning all columns of `A` are also in `B`).

I want to update a record with a specific `ID` in `B` with their data from `A` for all columns of `A`. This `ID` exists both in `A` and `B`.

Is there an `UPDATE` syntax or any other way to do that without specifying the column names, just saying *&quot;set all columns of A&quot;*?

I&#39;m using PostgreSQL, so a specific non-standard command is also accepted (however, not preferred).

SQL update fields of one table from fields of another one

Executing dynamic SQL as follows in Stored Procedure:

    DECLARE @sqlCommand nvarchar(1000)
    DECLARE @city varchar(75)
    SET @city = &#39;London&#39;
    SET @sqlCommand = &#39;SELECT COUNT(*) FROM customers WHERE City = @city&#39;
    EXECUTE sp_executesql @sqlCommand, N&#39;@city nvarchar(75)&#39;, @city = @city

How do I use the count(*) column value as return value in the SP?



Getting result of dynamic SQL into a variable for sql-server

I want to pass a table name as a parameter in a Postgres function. I tried this code:

    CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
    AS $$
        BEGIN
    	IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN
    	 return 1;
    	END IF;
        return 0;
        END;
    $$ LANGUAGE plpgsql;
    
    select some_f(&#39;table_name&#39;);

And I got this:

    ERROR:  syntax error at or near &quot;.&quot;
    LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
                                                                 ^
    
    ********** Error **********
    
    ERROR: syntax error at or near &quot;.&quot;

And here is the error I got when changed to this `select * from quote_ident($1) tab where tab.id=1`:

    ERROR:  column tab.id does not exist
    LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

Probably, `quote_ident($1)` works, because without the `where quote_ident($1).id=1` part I get `1`, which means something is selected. Why may the first `quote_ident($1)` work and the second one not at the same time? And how could this be solved?

Table name as a PostgreSQL function parameter

I was wondering if there was a way to do this in MS SQL Server 2005:

      DECLARE @theDate varchar(60)
      SET @theDate = &#39;&#39;&#39;2010-01-01&#39;&#39; AND &#39;&#39;2010-08-31 23:59:59&#39;&#39;&#39;

      SELECT	AdministratorCode, 
				SUM(Total) as theTotal, 
				SUM(WOD.Quantity) as theQty, 
				AVG(Total) as avgTotal, 
				(SELECT SUM(tblWOD.Amount)
					FROM tblWOD
					JOIN tblWO on tblWOD.OrderID = tblWO.ID
					WHERE tblWO.Approved = &#39;1&#39; 
					AND tblWO.AdministratorCode = tblWO.AdministratorCode
					AND tblWO.OrderDate BETWEEN @theDate
				)
     ... etc

Is this possible to do?

Declare Variable for a Query String

Why do I get this error

&gt; Procedure expects parameter &#39;@statement&#39; of type &#39;ntext/nchar/nvarchar&#39;.

when I try to use `sp_executesql`?

Why do I get &quot;Procedure expects parameter &#39;@statement&#39; of type &#39;ntext/nchar/nvarchar&#39;.&quot; when I try to use sp_executesql?

I&#39;ve spent a good amount of time coming up with solution to this problem, so in the spirit of [this post][1], I&#39;m posting it here, since I think it might be useful to others. 

If anyone has a better script, or anything to add, please post it.

Edit: Yes guys, I know how to do it in Management Studio - but I needed to be able to do it from within another application.


  [1]: https://stackoverflow.com/questions/21245/questions-vs-conveying-information

In SQL Server, how do I generate a CREATE TABLE statement for a given table?

In my stored procedure I declared two table variables on top of my procedure. Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of that procedure. I am using Sql Server 2008.

This is how my query looks like, 

    set @col_name =  &#39;Assoc_Item_&#39; 
                  + Convert(nvarchar(2), @curr_row1);

    set @sqlstat = &#39;update @RelPro set &#39; 
                 + @col_name 
                 + &#39; = (Select relsku From @TSku Where tid = &#39; 
                 + Convert(nvarchar(2), @curr_row1) + &#39;) Where RowID = &#39; 
                 + Convert(nvarchar(2), @curr_row);

    Exec(@sqlstat);

And I get the following errors, 

Must declare the table variable &quot;@RelPro&quot;.
Must declare the table variable &quot;@TSku&quot;.

I have tried to take the table outside of the string block of dynamic query but to no avail.




How to use table variable in a dynamic sql statement?

So I&#39;m writing a stored procedure in MS SQL Server 2008. It&#39;s a really long query and I have to write it dynamically, so I create a variable called `@Query` and make it of type `NVARCHAR(MAX)`. Now, I have been *told* that in modern versions of SQL Server, `NVARCHAR(MAX)` can hold a ridiculous amount of data, way more than the original 4000 character maximum. However, `@Query` is still getting truncated to 4000 characters when I try to print it out.

    DECLARE @Query NVARCHAR(max);
    SET @Query = &#39;SELECT...&#39; -- some of the query gets set here
    SET @Query = @Query + &#39;...&#39; -- more query gets added on, etc.
    
    -- later on...
    PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
    PRINT @Query      -- Truncates value to 4000 characters
    EXEC sp_executesql @Query -- totally crashes due to malformed (truncated) query

Am I doing something incorrectly, or am I completely wrong about how `NVARCHAR(MAX)` works?

All Dynamic Sql Solutions on Gang of Coders

Total of 16 Dynamic Sql Solutions