This code works properly because it is:
Not doing any Dynamic SQL
In order for SQL Injection to work, you have to build a query string (which you are not doing) and not translate single apostrophes (') into escaped-apostrophes ('') (those are escaped via the input parameters).
In your attempt to pass in a "compromised" value, the 'Male; ...
Back in 2007, I asked for an easy way to generate a CREATE TABLE script via T-SQL rather than using the UI or SMO. I was summarily rejected.
However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha:
CREATE TABLE dbo.whatcha
id INT IDENTITY(1,1),
You can use nested EXEC calls. The database context changed by the USE persists to the child batch.
DECLARE @DB SYSNAME
SET @DB = 'tempdb'
DECLARE @CreateViewStatement NVARCHAR(MAX)
SET @CreateViewStatement = '
USE '+ QUOTENAME(@DB) +';
CREATE VIEW [dbo].[MyTable] AS
SELECT 1 AS [Foo]
As others have said this is known as a PIVOT. There are several ways in which you can transform your data from rows into columns of data.
If you know the values ahead of time, then you can hard-code the values. Prior to the PIVOT function you would use an aggregate function with a CASE statement.
sum(case when [...
Ok, couple of things.
always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the first statement in the batch (and that will not be the case here).
always use semi-colon terminators - in this case they are useful in lieu of pretty carriage returns and indentation, but they are always wise to have.
always use ...
Simple with hstore
If you have the additional module hstore installed (instructions in link below), there is a surprisingly simple way to replace the value(s) of individual field(s) without knowing anything about other columns:
Basic example: duplicate the row with id = 2 but replace 2 with 3:
INSERT INTO people
SELECT (p #= hstore('id', '3')).* FROM ...
I am struggling to find the reason of the arithmetic overflow. why is it happening?
Most likely the metadata is returning some unexpected values that your code cannot handle. For example:
-- Example values returned from sysfiles and FILEPROPERTY
@size integer = 1,
@spaceused integer = 10000;
-- The essence of the code in the question
Just to get these out of the way:
Technically speaking, both of these options are "dynamic" / ad hoc queries that are not parsed / validated until they are submitted. And both are susceptible to SQL Injection since they are not parameterized (though with the SQLCMD scripts, if you are passing in a variable from a CMD script then you do have an opportunity ...
George's answer does solve your problem, but it leaves you wide open to SQL injection attacks.
While converting an INT to a VARCHAR 11 is likely not going to cause any issues, sysname is the equivalent of an NVARCHAR 128, and you can jam a lot of extra code in there.
To make your code totally safe, you'd want to do this:
DECLARE @stringsvar NVARCHAR(1000) ...
The PRINT statement inside the WHILE loop does execute in the order you expect, but the output is buffered before sys.sp_executesql returns. Implementation details mean the buffered output is reversed.
Using RAISERROR (@cmd, 0, 1) WITH NOWAIT; instead of PRINT forces the buffer to flush after each call, giving you the results in the order you expect. IIRC ...
I think you meant to use sp_executesql:
EXEC sp_Executesql @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOut = @TableNotExists OUTPUT
And as JonSeigel pointed out in the comment, you have mis-spelled a parameter in your statement:
SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE =...
The fundamental issue is that TSQL cannot implicitly convert datetime (or integer or floating point) into character data types. It actually goes backwards, it attempts to implicitly convert the character data to the datetime (int/floating point value) based on the data type precedence rules. This is what your error message is telling you by the way, it ...
The reason the SQL statements are getting wrapped with sp_executesql is the setting of the SqlCommand.Commandtype property and passing any Parameters to the command.
SqlCommand cmd = new SqlCommand("proc1", con);
cmd.CommandType = CommandType.StoredProcedure;
Simple case, static SQL
The non-dynamic solution with crosstab() for the simple case:
SELECT * FROM crosstab(
'SELECT b.x, f.name, f.x * b.x AS prod
FROM foo f, bar b
ORDER BY 1, 2'
) AS ct (x int, "A" int, "B" int, "C" int, "D" int, "E" int
, "F" int, "G" int, "H" int, "I" int, "J" int);
I order resulting columns by foo....
Sure, there is a way - there's always a way...
If you declare variable and store in it the database and the procedure to run, you can exec it, with parameters.
declare @db sysname = 'master.sys.sp_executesql';
exec @db N'select db_name()';
set @db = 'msdb.sys.sp_executesql';
exec @db N'select db_name()';
It is ...
To drop all functions (including aggregates) in a given schema (be careful with this!):
For Postgres 11:
- Procedures were added
- The system catalog pg_proc slightly changed: prokind replaces proisagg and proiswindow - and also tags functions and the new procedures):
SELECT INTO _sql
string_agg(format('DROP %s %...
You had to pick the spot where all possible complications come together.
SQL (or PL/pgSQL) does not allow to parameterize identifiers. You need dynamic SQL with EXECUTE for that.
But the special plpgsql variable NEW in trigger functions is not visible inside dynamic code executed with EXECUTE.
And it's further complicated by passing column names as ...
What's the best we can do in PostgreSQL without a "column definition
list" to generate that kind of presentation?
If you frame this as a presentation problem, you might consider a post-query presentation feature.
Newer versions of psql (9.6) come with \crosstabview, showing a result in crosstab representation without SQL support (since SQL can't produce ...
You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here.
CREATE OR REPLACE FUNCTION insert_records_for_notification(
, _state text
, _district text
, _bloodgroup text
, _status text
, _approverejectstatus text
, _emailsubject text
Strict DRY doesn't really apply to databases
I've seen DRY is used as justification to create views for "re-use". Then we have views joining views etc and piss poor performance.
Generally, similar queries will be used in different ways. One may have an aggregate, one may not, filters will be different (as above). The similarity doesn't justify dynamic SQL ...
Are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them?
You need a real stored procedure (CREATE PROCEDURE) to benefit from temporary table caching. This includes temporary stored procedures (#procname).
Point #3 on this blog post ...
Session-level changes made in a sub-process (i.e. EXEC / sp_executesql) go away when that sub-process ends. This covers USE and SET statements as well as any local temporary tables created in that sub-process. Creation of global temporary tables will survive the sub-process, and so will modifications made to local temporary tables that exist prior to the sub-...
As Martin Smith said, you need to pivot the data, whether with an explicit PIVOT as referenced or something like this (SQL Fiddle):
, SUM(Case When Month='Jan' Then Amount Else 0 End) MonthJan
, SUM(Case When Month='Apr' Then Amount Else 0 End) MonthApr
FROM Result GROUP BY SiteCode;
If you just need results from multiple schemas, you can re-use the same query string and set the search_path in between:
SET search_path = u111, public;
SELECT * FROM foo;
SET search_path = u222, public;
SELECT * FROM foo;
If you need to combine results from multiple schemas (probably your use-case), you can either build the statement in your client or ...
For certain types of parameterization, it's not going to matter if you use EXEC() or sp_executesql, because some things can't be parameterized anyway. For example, you expressed in the comments (please update your question to be more specific about your requirements!) that you are parameterizing table names, but these can't be parameterized because they need ...
There isn't any definitive answer for this problem.
You've correctly identified some of the possible tradeoffs (single table=simpler/possibly slower/more contention?, multiple tables=more complicated/faster, maybe?/harder to use in other contexts?).
The general advice is to start with the more straight-forward single-table solution, then break them out if ...
There is nothing wrong with using dynamic SQL if you must. In fact in some circumstances it is the only option that you have. It is more of a recommendation not to use it as yes it can lead to a SQL injection if your input is not sanitized, and yes using dynamic SQL in modules that get called often can be detrimental to it's performance.
I don't think ...
You can do something along the following lines:
test=> CREATE OR REPLACE FUNCTION temptabl(cnt integer)
RETURNS SETOF integer AS
CREATE TEMPORARY TABLE tmp_container ON COMMIT DROP AS
FROM generate_series(1, cnt) t(a);
IF (SELECT count(1) FROM tmp_container) > 5
RETURN QUERY SELECT a FROM ...
Going to be pretty tough to get consensus on what "best" means, since there is a trade-off with dynamic SQL (you gain some plan stability for each version of the query, depending on parameters, but you lose things like readability, IntelliSense, etc). I've used the dynamic SQL route in many implementations and I highly recommend it.
Since you will be ...