Try creating an inline table-valued function. Example:

    CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER)
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT Field1, Field2
        FROM SomeTable
        WHERE Field3 = @Parameter1
    )
    
    -- Then call like this, just as if it&#39;s a table/view just with a parameter
    SELECT * FROM dbo.fxnExample(1)

If you view the execution plan for the SELECT you will not see a mention of the function at all and will actually just show you the underlying tables being queried. This is good as it means statistics on the underlying tables will be used when generating an execution plan for the query. 

The thing to avoid would be a multi-statement table valued function as underlying table statistics will not be used and can result in poor performance due to a poor execution plan.&lt;br/&gt;
Example of what to **avoid**:

    CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER)
        RETURNS @Results TABLE(Field1 VARCHAR(10), Field2 VARCHAR(10))
    AS
    BEGIN
        INSERT @Results
        SELECT Field1, Field2
        FROM SomeTable
        WHERE Field3 = @Parameter1
      
        RETURN
    END

Subtly different, but with potentially big differences in performance when the function is used in a query.

No, you cannot. But you can create a [user defined table function][1].


  [1]: http://msdn.microsoft.com/en-us/library/ms186755.aspx

in fact there exists one trick:

    create view view_test as

    select
      * 
    from 
      table 
    where id = (select convert(int, convert(binary(4), context_info)) from master.dbo.sysprocesses
    where
    spid = @@spid)

...
in sql-query:

    set context_info 2
    select * from view_test

will be the same with 

    select * from table where id = 2

but using udf is more acceptable




As astander has mentioned, you can do that with a UDF. However, for large sets using a **scalar** function (as oppoosed to a inline-table function) the performance will stink as the function is evaluated row-by-row. As an alternative, you could expose the same results via a stored procedure executing a fixed query with placeholders which substitutes in your parameter values.

([Here&#39;s][1] a somewhat dated but still relevant article on row-by-row processing for scalar UDFs.)

**Edit:** comments re. degrading performance adjusted to make it clear this applies to scalar UDFs.


  [1]: http://www.sqlmag.com/article/user-defined-function-udf/beware-row-by-row-operations-in-udf-clothing.aspx

no. You can use UDF in which you can pass parameters.


I was hoping someone could explain the nuances of the __user macro used in the linux kernel source.

First of all, the macro:

    # define __user         __attribute__((noderef, address_space(1)))

Now, after some googling I read that this macro allows one to designate a pointer as belonging to the user address space, and that it should not be dereferenced.

I may be missing some obvious facts, but could someone please explain the implications of such a macro? For instance, what is a good example of where this macro would be of use? Again, forgive me if I am missing something obvious.

To put this in some context, I came accross the macro while examining some USB code (linux/usbdevice_fs.h). I am only looking for a general understanding of this macros( or others like it) use within the kernel.

Thanks for looking!

What are the implications of the linux __user macro?

In .NET there is the `null` reference, which is used everywhere to denote that an object reference is empty, and then there is the `DBNull`, which is used by database drivers (and few others) to denote... pretty much the same thing. Naturally, this creates a lot of confusion and conversion routines have to be churned out, etc.

Why did the original .NET authors decide to make this? To me it makes no sense. Their documentation makes no sense either:

&gt; The DBNull class represents a nonexistent value. In a database, for example, a column in a row of a table might not contain any data whatsoever. That is, the column is considered to not exist at all instead of merely not having a value. A DBNull object represents the nonexistent column. Additionally, COM interop uses the DBNull class to distinguish between a VT_NULL variant, which indicates a nonexistent value, and a VT_EMPTY variant, which indicates an unspecified value. 

What&#39;s this about a &quot;column not existing&quot;? A column exists, it just doesn&#39;t have a value for the particular row. If it didn&#39;t exist, I&#39;d get an exception trying to access the specific cell, not a `DBNull`! I can understand the need to differentiate between `VT_NULL` and `VT_EMPTY`, but then why not make a `COMEmpty` class instead? That would be a much neater fit in the whole .NET framework.

Am I missing something? Can anyone shed some light why `DBNull` was invented and what problems it helps to solve?

What is the point of DBNull?

Can we create parameterized VIEW in SQL Server 2008.

Or Any other alternative for this ?

Create parameterized VIEW in SQL Server 2008

<p>Can we create parameterized VIEW in SQL Server 2008.</p>
<p>Or Any other alternative for this ?</p>


    Name  	Value  	AnotherColumn
    -----------
    Pump 1	8000.0	Something1
    Pump 1	10000.0	Something2
    Pump 1	10000.0	Something3
    Pump 2	3043	Something4
    Pump 2	4594	Something5
    Pump 2	6165	Something6

My table looks something like this. I would like to know how to select max value for each pump.

    select a.name, value from out_pumptable as a,
    (select name, max(value) as value from out_pumptable where group by posnumber)g where and g.value = value
this code does the job, but i get two entries of Pump 1 since it has two entries with same value.

Select max value of each group

I have been experimenting with Redis and MongoDB lately and it would seem that there are often cases where you would store an array of __id&#39;s__ in either MongoDB or Redis. I&#39;ll stick with Redis for this question since I am asking about the MySQL __IN__ operator.

I was wondering how performant it is to list a large number (300-3000) of __id&#39;s__ inside the IN operator, which would look something like this:

    SELECT id, name, price
    FROM products
    WHERE id IN (1, 2, 3, 4, ...... 3000)

Imagine something as simple as a __products__ and __categories__ table which you might normally JOIN together to get the __products__ from a certain __category__. In the example above you can see that under a given category in Redis ( `category:4:product_ids` ) I return all the product ids from the category with id 4, and place them in the above `SELECT` query inside the `IN` operator.

How performant is this?

Is this an &quot;it depends&quot; situation? Or is there a concrete &quot;this is (un)acceptable&quot; or &quot;fast&quot; or &quot;slow&quot; or should I add a `LIMIT 25`, or doesn&#39;t that help?

    SELECT id, name, price
    FROM products
    WHERE id IN (1, 2, 3, 4, ...... 3000)
    LIMIT 25

Or should I trim the array of product id&#39;s returned by Redis to limit it to 25 and only add 25 id&#39;s to the query rather than 3000 and `LIMIT`-ing it to 25 from inside the query? 

    SELECT id, name, price
    FROM products
    WHERE id IN (1, 2, 3, 4, ...... 25)

Any suggestions/feedback is much appreciated!

MySQL &quot;IN&quot; operator performance on (large?) number of values

I currently have a large SQL statement which i add the following line to in order to get the total cash for each transaction ID (which are unique):

    select sum(cash) from Table a where a.branch = p.branch 
    and a.transID = p.transID) TotalCash

and i now need to do the same but only total the cash values that have a valuedate within the last month, so i have something like this:

    select sum(CASE ValueDate WHEN &gt; @startMonthDate THEN cash ELSE NULL END) 
    from Table a where a.branch = p.branch and a.transID = p.transID) TotalMonthCash

Sorry that I dont have the whole statement, but it is really long and specific to the context of the stored procedure but was hoping someone would know what i mean?


SQL sum with condition

I am writing this query to find duplicate CTN Records in table1. So my thinking is if the CTN_NO appears more than twice or higher , I want it shown in my SELECT * statement output on top.

I tried the following sub-query logic but I need pulls 

      SELECT *
             table1 
       WHERE S_IND=&#39;Y&#39;
         and CTN_NO = (select CTN_NO 
                         from table1 
                        where S_IND=&#39;Y&#39; 
                          and count(CTN_NO) &lt; 2);
    order by 2

Find duplicate entries in a column

I&#39;m trying to figure out how to write a &#39;not in&#39; style query in django.
For example, the query structure I&#39;m thinking of would look like this.

    select table1.* 
    from table1
    where table1.id not in 
    (
      select table2.key_to_table1
      from table2 
      where table2.id = some_parm 
    )

What would the django syntax look like assuming models called table1 and table2?

Django in / not in query

I need to view the transaction logs of a database on [SQL Server 2008][1] in order to find a delete transaction and hopefully roll it back.

Unfortunately I have no clue where to start, and I&#39;m finding it difficult to determine which are **good** articles on Google. 

What should I do?

  [1]: http://en.wikipedia.org/wiki/Microsoft_SQL_Server#SQL_Server_2008


How to view transaction logs in SQL Server 2008

I have `SQL Server 2008`, SQL Server Management Studio.

I need to select data from `Table1` in database1. Then I have to edit some values in the results and `insert values into Table1` in database2. 

Or let me put it other way. 

How can I convert the data in one table into `insert script`.

Converting Select results into Insert script - SQL Server

Would any one please differentiate what is best to use SQLite or SQL Server? I was using XML file as a data storage ADD, delete , update.. Some one suggested to use SQLite for fast operation but I am not familier with SQLite I know SQL Server.

SQLite vs SQL Server

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?

According to [this forum discussion][1], SQL Server (I&#39;m using 2005 but I gather this also applies to 2000 and 2008) silently truncates any `varchar`s you specify as stored procedure parameters to the length of the varchar, even if inserting that string directly using an `INSERT` would actually cause an error.  eg. If I create this table:

    CREATE TABLE testTable(
    	[testStringField] [nvarchar](5) NOT NULL
    )

then when I execute the following:

    INSERT INTO testTable(testStringField) VALUES(N&#39;string which is too long&#39;)

I get an error:

    String or binary data would be truncated.
    The statement has been terminated.

Great.  Data integrity preserved, and the caller knows about it.  Now let&#39;s define a stored procedure to insert that:

    CREATE PROCEDURE spTestTableInsert
    	@testStringField [nvarchar](5)
    AS
    	INSERT INTO testTable(testStringField) VALUES(@testStringField)
    GO

and execute it:

    EXEC spTestTableInsert @testStringField = N&#39;string which is too long&#39;

No errors, 1 row affected.  A row is inserted into the table, with `testStringField` as &#39;strin&#39;.  SQL Server silently truncated the stored procedure&#39;s `varchar` parameter.

Now, this behaviour might be convenient at times but I gather there is NO WAY to turn it off.  This is extremely annoying, as I *want* the thing to error if I pass too long a string to the stored procedure.  There seem to be 2 ways to deal with this.

First, declare the stored proc&#39;s `@testStringField` parameter as size 6, and check whether its length is over 5.  This seems like a bit of a hack and involves irritating amounts of boilerplate code.

Second, just declare ALL stored procedure varchar parameters to be `varchar(max)`, and then let the `INSERT` statement within the stored procedure fail.

The latter seems to work fine, so my question is: is it a good idea to use `varchar(max)` ALWAYS for strings in SQL Server stored procedures, if I actually want the stored proc to fail when too long a string is passed?  Could it even be best practice?  The silent truncation that can&#39;t be disabled seems stupid to me.


  [1]: http://www.pcreview.co.uk/forums/thread-1241669.php


Content Type	Original Author	Original Content on Stackoverflow
Question	Sreekumar P	View Question on Stackoverflow
Solution 1 - Sql	AdaTheDev	View Answer on Stackoverflow
Solution 2 - Sql	Adriaan Stander	View Answer on Stackoverflow
Solution 3 - Sql	heximal	View Answer on Stackoverflow
Solution 4 - Sql	davek	View Answer on Stackoverflow
Solution 5 - Sql	Pankaj Agarwal	View Answer on Stackoverflow

Create parameterized VIEW in SQL Server 2008

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

Solution 5 - Sql

What is the point of DBNull?

What are the implications of the linux __user macro?

Attributions