Using SQL Server 2014...  I'm trying to convert this code to be used in a function...   SET @sqlCommand = N'SELECT @text = ' + @fieldname + ' FROM ' + @tablename +' WHERE ID=''' + CONVERT(NVARCHAR(200), @ID)  + '''' EXECUTE sp_executesql...   ...but functions doesn't accept sp_executesql.  How can I in a function get data from a table where the tablename is coming from a parameter?
102930
I will preface this question by saying, I do not think it is solvable. I also have a workaround, I can create a stored procedure with an OUTPUT to accomplish this, it is just easier to code the sections where I need this checksum using a function.  This code will not work because of the Exec SP_ExecuteSQL @SQL calls. Anyone know how to execute dynamic SQL in a function? (and once again, I do not think it is possible. If it is though, I'd love to know how to get around it!)  Create Function Get_Checksum (     @DatabaseName      varchar(100),     @TableName         varchar(100) ) RETURNS FLOAT AS BEGIN   Declare @SQL        nvarchar(4000)  Declare @ColumnName varchar(100)  Declare @i          int  Declare @Checksum   float  Declare @intColumns table (idRecord int identity(1,1), ColumnName varchar(255))  Declare @CS         table (MyCheckSum bigint)   Set @SQL =          'Insert Into @IntColumns(ColumnName)' + Char(13) +          'Select Column_Name' + Char(13) +         'From   ' + @DatabaseName + '.Information_Schema.Columns (NOLOCK)' + Char(13) +         'Where  Table_Name = ''' + @TableName + '''' + Char(13) +         '       and Data_Type = ''int'''    -- print @SQL   exec sp_executeSql @SQL   Set @SQL =          'Insert Into @CS(MyChecksum)' + Char(13) +          'Select '   Set @i = 1   While Exists(        Select 1        From   @IntColumns        Where  IdRecord = @i)  begin        Select @ColumnName = ColumnName        From   @IntColumns        Where  IdRecord = @i         Set @SQL = @SQL + Char(13) +              CASE WHEN @i = 1 THEN                   '    Sum(Cast(IsNull(' + @ColumnName + ',0) as bigint))'                  ELSE                  '    + Sum(Cast(IsNull(' + @ColumnName + ',0) as bigint))'             END         Set @i = @i + 1  end   Set @SQL = @SQL + Char(13) +        'From ' + @DatabaseName + '..' + @TableName + ' (NOLOCK)'   -- print @SQL   exec sp_executeSql @SQL   Set @Checksum = (Select Top 1 MyChecksum From @CS)   Return isnull(@Checksum,0)  END GO
52590
When I have a Set of Id's and I want to do a dynamic SOQL query that I want to use it in for an IN comparison, I have to convert the Set to string in the format of:  ('id1','id2',id3',...)   The way I do it works, but is not particularly elegant, so I'm wondering if anyone has come up with a better way. Am I missing something? Is there something out there that can just turn a set or list into the properly formatted string?   Here's my code (yes, my example isn't dynamic SOQL, but I've used this several times in dynamic SOQL):   Map&lt;Id,Account&gt; accts = new Map&lt;Id,Account&gt;([select Id from Account]);  String idString = '(\''; for (Id thisId : accts.keyset()) {     idString += thisId + '\',\''; } idString = idString.substring(0,idString.length()-2); //&lt;--this is the bit I hate doing idString += ')';  String q = 'select id from Contact where AccountId in ' + idString;  List&lt;Contact&gt; cts = Database.query(q);
120263
I'm trying to implement optional parameters in a stored procedure that I have but I am running into a problem. Here's a simplified query to illustrate the issue:  SET ANSI_NULLS OFF  DECLARE @MiddleName VARCHAR(20); SET @MiddleName = NULL;  SELECT * FROM [Customer] WHERE [LastName] = 'Torres' AND [MiddleName] = COALESCE(@MiddleName, [MiddleName])   When I run this query I need to get one row back because one Torres has NULL in the [MiddleName] column. But the query returns zero rows. Using IFNULL() produces the same result. From researching COALESCE, I was under the impression that NULL would be returned if all expressions are NULL. As I am not a SQL expert I assume that I am missing something, but what is it.....  Thanks in advance for any help.
24101
Using C# and System.Data.SqlClient, is there a way to retrieve a list of parameters that belong to a stored procedure on a SQL Server before I actually execute it?  I have an a "multi-environment" scenario where there are multiple versions of the same database schema. Examples of environments might be "Development", "Staging", &amp; "Production". "Development" is going to have one version of the stored procedure and "Staging" is going to have another.  All I want to do is validate that a parameter is going to be there before passing it a value and calling the stored procedure. Avoiding that SqlException rather than having to catch it is a plus for me.  Joshua
119920
I'm working on an application for work that is going to query our employee database. The end users want the ability to search based on the standard name/department criteria, but they also want the flexibility to query for all people with the first name of "James" that works in the Health Department. The one thing I want to avoid is to simply have the stored procedure take a list of parameters and generate a SQL statement to execute, since that would open doors to SQL injection at an internal level.  Can this be done?
93666
How would I return multiple values (say, a number and a string) from a user-defined function in SQL Server?
29132
I'm looking at creating a basic ORM (purely for fun), and was wondering, is there a way to return the list of tables in a database and also the fields for every table?  Using this, I want to be able to loop through the result set (in C#) and then say for each table in the result set, do this (e.g. use reflection to make a class that will do or contain xyz).  Further to this, what are some good online blogs for SQL Server? I know this question is really about using system SPs and databases in Sql Server, and I am ok with general queries, so I'm interested in some blogs which cover this sort of functionality.  Thanks
19638
I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (, , , , and ).  Is there a silver-bullet syntax coming from an SQL standard (for example, ) that would allow me to insert the values without worrying about the underlying database?
75489
I am using IBM DB2.  So, I am retrieving a column named SSN from a table. I have to apply the following regex on that column:  ([1-57-8][0-9]{2} |0([1-9][0-9] |[0-9][1-9]) |6([0-57-9][0-9] |[0-9][0-57-9])) ([1-9][0-9]|[0-9][1-9])([1-9]\d{3}|\d[1-9]\d{2}|\d{2}[1-9]\d|\d{3}[1-9])   Someone suggested to me that I have to write a function/procedure in the query to validate that the SSN picked from the db follows this regex else it should not be stored in the result set of the query.  I don't know how to write a function or a procedure in SQL, can you please help me out here?
62190
How can I create an Oracle stored procedure which accepts a variable number of parameter values used to feed a IN clause?  This is what I am trying to achieve. I do not know how to declare  in PLSQL for passing a variable list of primary keys of the rows I want to update.  FUNCTION EXECUTE_UPDATE   ( &lt;parameter_list&gt;    value IN int)   RETURN  int IS BEGIN      [...other statements...]     update table1 set col1 = col1 - value where id in (&lt;parameter_list&gt;)       RETURN SQL%ROWCOUNT ; END;   Also, I would like to call this procedure from C#, so it must be compatible with .NET capabilities.   Thanks, Robert
21568
$sql = "SELECT Kill FROM tbl_pvporderview";   Problem is that I end up with: Incorrect syntax near the keyword 'Kill'.  Because kill is a T-SQL command... any way to bypass it?  I can't change the column name because it's used by the software a lot and I cant change the software that's using the database.  So it simply fails if I use sqlserv to select data from that column. '' or "" wont help.  The complete statement would be:  $sql = "SELECT serial,Kill FROM tbl_pvporderview WHERE Kill &gt; (?) ORDER BY Kill DESC ";
57994
I've got data in SQL Server 2005 that contains HTML tags and I'd like to strip all that out, leaving just the text between the tags. Ideally also replacing things like &amp;lt; with &lt;, etc. Is there an easy way to do this or has someone already got some sample T-SQL code? I don't have the ability to add extended stored procs and the like, so would prefer a pure T-SQL approach (preferably one backwards compatible with SQL 2000). I just want to retrieve the data with stripped out HTML, not update it, so ideally it would be written as a user-defined function, to make for easy reuse. So for example converting this: &lt;B&gt;Some useful text&lt;/B&gt;&amp;nbsp; &lt;A onclick=&quot;return openInfo(this)&quot;    href=&quot;http://there.com/3ce984e88d0531bac5349&quot;    target=globalhelp&gt;    &lt;IMG title=&quot;Source Description&quot; height=15 alt=&quot;Source Description&quot;          src=&quot;/ri/new_info.gif&quot; width=15 align=top border=0&gt; &lt;/A&gt;&amp;gt;&amp;nbsp;&lt;b&gt;more text&lt;/b&gt;&lt;/TD&gt;&lt;/TR&gt;  to this: Some useful text &gt; more text
18114
I have a method with an out parameter that tries to do a type conversion. Basically:  public void GetParameterValue(out object destination) {     object paramVal = "I want to return this. could be any type, not just string.";      destination = null; // default out param to null     destination = Convert.ChangeType(paramVal, destination.GetType()); }   The problem is that usually someone would call this like:  string output; GetParameterValue(output);   This will fail because of:  destination.GetType()   destination is null, so we can't call .GetType() on it. We also can not call:  typeof(destination)   because destination is a variable name not a type name.  So is there any way to get the type of an object that is set to null? I would think there would have to be a way to know what type a storage location is without it being assigned anything.    Just to give a bit more info, I am trying to make a utility method that will grab the output parameters of an Oracle stored procedure. The issue is that DbParameter.Value is of type object.  What would be ideal would be for the developers to do something like:  string val = GetParameterValue("parameterName");   The notable thing is that there is no casting of types. In practice, you don't know the lparam of the "equals", so I went with:  string val; GetParameterValue("parameterName", out val);   And figured within the method, I would know the destination type of the output variable. I guess that was a bad assumption. As an alternative, I also wrote the method:  public T GetParameterValue&lt;T&gt;(string paramName)   So the developers can do:  string val = GetParameterValue&lt;string&gt;("parameterName");   I find the explicit "string" declaration to be repetitive, especially since in practice, the destination if probably an object property and the oracle data type could change (think ORM):  MyObj.SomeProp = GetParameterValue&lt;MyObj.SomeProp.GetType()&gt;("parameterName");   But again, if MyObj.SomeProp is null, that .GetType() call fails. The VM has to know the type of MyObj.SomeProp, even when its null, right? or else how would it catch cast exceptions?    To partially solve my own problem, I can do:  MyObj.SomeProp = GetParameterValue&lt;typeof(MyObj).GetField("SomeProp").GetType()&gt;("parameterName");   The whole idea was to not have to explicitly use the Type in more than one place, so that if the data type changes, it only has to be changed in the destination object (MyObj.SomeProp) and in the DB. There has to be a better way...
2711
I have this code:  DECLARE @MyTable AS TABLE (    [Month] INT,    Salary INT );  INSERT INTO @MyTable VALUES (1,2000), (1,3100);  SELECT [Month], Salary FROM @MyTable;   Output:    I want to concat the Salary (grouping by month) so that it will be NVARCHAR like this: '2000,3100'  How would I do this efficiently?
69850
I have the following code:  Using cmd As SqlCommand = Connection.CreateCommand     cmd.CommandText = "UPDATE someTable SET Value = @Value"     cmd.CommandText &amp;= " WHERE Id = @Id"     cmd.Parameters.AddWithValue("@Id", 1234)     cmd.Parameters.AddWithValue("@Value", "myValue")     cmd.ExecuteNonQuery End Using   I wonder if there is any way to get the final SQL statment as a String, which should look like this:  UPDATE someTable SET Value = "myValue" WHERE Id = 1234   If anyone wonders why I would do this:   for logging (failed) statements for having the possibility to copy &amp; paste it to the Enterprise Manager for testing purposes
12291
I have a stored procedure that returns 80 columns, and 300 rows.  I want to write a select that gets 2 of those columns.  Something like   SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'   When I used the above syntax I get the error:      "Invalid Column Name".   I know the easiest solution would be to change the stored procedure, but I didn't write it, and I can't change it.    Is there any way to do what I want?   I could make a temp table to put the results in, but because there are 80 columns so I would need to make an 80 column temp table just to get 2 columns.  I wanted to avoid tracking down all the columns that are returned. I tried using WITH SprocResults AS .... as suggested by Mark, but I got 2 errors      Incorrect syntax near the keyword 'EXEC'.Incorrect syntax near ')'.  I tried declaring a table variable and I got the following error      Insert Error: Column name or number of supplied values does not match table definition  If I try  SELECT * FROM EXEC MyStoredProc 'param1', 'param2' I get the error :     Incorrect syntax near the keyword 'exec'.
74456
I want to write a query like this:  SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice) FROM Order o   But this isn't how the MAX function works, right?  It is an aggregate function so it expects a single parameter and then returns the MAX of all rows.     Does anyone know how to do it my way?