how do I assign a string to a parameter Im passing to a select statement?

Hello,

I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product.

I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here:

http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx

I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.

I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:

WHERE (OrderStatus IN ((select value from dbo.fn_Split(@.StatusList,','))) OR @.StatusList IS NULL)

but now I don't know how to assign the string value to the parameter, say to '@.solution_id'.

my current select statement which was generated by Visual Studio 2005 looks like this:

SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @.solution_ID)

...but this only pulls results for the first item checked in the checkboxlist.

Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.

hi

First make sure you have createddbo.fn_Split .

SELECT [test], [owner], [date]FROM [test_table]WHERE ([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)

I am not sure "OR @.solution_IDISNULL" should be added,you have to decide it according to your logic.

You are required to pass @.solution_ID to the statement(1,3,4,6 etc) then you can get corresponding test.

Hope this helps.

|||

Thanks for your response. If I'm following you, I do understand that I need to pass @.solution_ID to the select statement like you showed. I have a string of values that I created from iterating through CheckBoxList to find selected boxes. My question is, how do I assign the value of this string to @.solution_ID?

Regards,

Daniel

|||

Assume you have checkboxlist Check1, using following code to get @.solution_ID :

for (int i = 0; i < Check1.Items.Count; i++)
{
if (Check1.Items[i].Selected)
{
// List the selected items
solution_ID = solution_ID + Check1.Items[i].Text;
solution_ID = solution_ID +",";
}
}

Then connect with DB:

SqlCommand sqlcmd = new SqlCommand("SELECT [test], [owner], [date]FROM [test_table]WHERE
([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)", sqlconn);
sqlcmd.Parameters.AddWithValue("@.solution_ID",solution_ID);
sqlconn.Open();
SqlDataReader sdr = sqlcmd.ExecuteReader();

.............

You 'd bette put bold sql script into a stored procedure.

hope this helps.

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttid
Look at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid|||Oh, I see. I have to use Having and it's after the Group. It works great.
Thank you very much and have a great day!
"Geoff N. Hiten" wrote:
> Look at the "Having" clause of the select statement. "Having" works on
> "Group By" much like the 'Where" works on "From". In your case, you would
> add something like:
> HAVING Sum(Amount) > 0
> after your Group By section.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> > Hi
> > I'm builing this string dynamically in C# program. I need to add one more
> > filtering condition of "Sum(Amount) >0" to the Select statement. I added
> > it
> > but I got an error message. Can someone help on how to do this in one
> > select
> > statment?
> >
> > Thanks, Alpha
> >
> > select exttid,sum(amount) as FeeBalance from tblLedger
> > where exttid in
> > (select distinct(exttid) from tblBilling b
> > where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> > and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> > and void = 0
> > group by exttid
>
>

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one selec
t
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttidsql

how do I access an error cause inside an exec statement

Hello:
how do I access an error code when
SELECT @.RESULTS EXEC('DBCC DBREINDEX('''+@.NAME+''') ')
fails because the database..table does not exists ?
@.RESULTS comes back with nothing but
I get
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'Internet_Forms.test'. Check
sysobjects.
(Problem since @.NAME is populated from sysobjects.....)
Thanks
TThe batch is aborted after encountering this specific error, so you cannot
followup and catch the error. One thing you could do is check if the table
exists like so:
if object_id('mytable') is not null
begin
<o.k. do something>
end
else
begin
<table does not exist>
end
The following is a good tutorial on the subject:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:%23VCo9XZoFHA.1468@.TK2MSFTNGP12.phx.gbl...
> Hello:
> how do I access an error code when
> SELECT @.RESULTS EXEC('DBCC DBREINDEX('''+@.NAME+''') ')
> fails because the database..table does not exists ?
> @.RESULTS comes back with nothing but
> I get
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'Internet_Forms.test'. Check
> sysobjects.
> (Problem since @.NAME is populated from sysobjects.....)
> Thanks
> T
>|||begin transaction
run querry
queryy @.@.error
commit when no error
rollback if erro
"Support" wrote:

> Hello:
> how do I access an error code when
> SELECT @.RESULTS EXEC('DBCC DBREINDEX('''+@.NAME+''') ')
> fails because the database..table does not exists ?
> @.RESULTS comes back with nothing but
> I get
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'Internet_Forms.test'. Check
> sysobjects.
> (Problem since @.NAME is populated from sysobjects.....)
> Thanks
> T
>
>|||@.@.error returns noting becuase of JT's reason...
That's the problem
Thanks
"jose g. de jesus jr mcp, mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:B6A8654B-67F3-47F0-A93E-24D7E77F02F9@.microsoft.com...
> begin transaction
> run querry
> queryy @.@.error
> commit when no error
> rollback if erro
>
>
> "Support" wrote:
>

How do i ?! Basic Select Statement

I have an sql data source..select command is something like this.

Select password from users where username = Username.Text

how can i retrieve the password value and save it into a string?! to make a comparison between password value and a textbox Password.Text

i know its a silly question but i took me long time and i still can't find the ans

We are going to need a little more context to evaluate your exact situation, and how to do what you ask, but here's 1 method:

Dim sqlConn as new SqlConnection("connection string here")

Dim sqlCmd as new SqlCommand("SELECT password from users where username = @.username", SqlConn)

sqlCmd.Paramaters.AddwithValue("@.username", username.text)

Dim DA as new SqlDataAdapter(SqlCmd)

Dim DT as new DataTable

DA.Fill(DT)

Dim myString as string = DT.rows(0).item("password").value ' This line may not be totally correct, doing it from memory and will edit it later

If myString.tolower = stringToCompare.tolower then

'Password is valid

else

'Password could be valid - but something threw you into the else statment, maybe an empty string.

end if

|||

Connection string and select command are already defined in the SqlDataSource.

how can you use the SqlDataSource to extract the value of the password?

i tried to use a hidden GridView but i didn't know how to access a specific cell

by the way i am using C#

|||

The above example is a much cleaner method of getting information like this. Putting a hidden gridview on your page is a VERY heavy method of trying to extract a single value, but it can be done.

Please see this article on how to extract a Datatable from the SqlDataSource control. Once you've done that, you can access a specific row of your datatable as mentioned above.

http://msmvps.com/blogs/egoldin/archive/2006/12/27/how-to-get-datatable-out-of-sqldatasource.aspx

I really have to recomend against this method though. If you insist on using the SqlDataSouce control to get this done, consider putting two hidden textbox on the page, and binding the value that comes back from the database to the hidden textbox, then use a compare validator and only let the user continue if page.isvalid.

How do i ?! Basic Select Statement

I have an sql data source..select command is something like this.

Select password from users where username = Username.Text

how can i retrieve the password value and save it into a string?!

to make a comparison between password value and a textbox Password.Text

i know its a silly question but i took me long time and i still can't find the answer

Hello Ahmad,

Here's some code that should help you out... I'd be happy to clarify anything if needed.

using System.Data;

using System.Data.SqlClient;

string userPassword;

string userName;

SqlConnection cnn = new SqlConnection("Data Source = (local); Initial Catalog = nameofdatabase; Integrated Security = True");

SqlCommand cmd = new SqlCommand("select Password From Users Where username = @.username", cnn);

SqlParameter UserName = new SqlParameter("@.username", userName);

cmd.Parameters.Add(UserName);

cnn.Open();

string password = (string)cmd.ExecuteScalar();

cnn.Close();

cnn.Dispose();

if (userPassword == password)

{

// success! Do something here

}

else

{

// Failure...

}

Regards.

sql

How do a pass a 'Where' string clause to a stored procedure

I need to be able to construct a SELECT statement in a stored procedure and
concatenate a CSV list of values passed as an input parm to the stored
procedure. How can I do this without declaring a variable for the Select,
then executing it. I need to use this with Reporting Services so unless the
Select fields are available in the query, RS won't work.See
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Kitty" <Kitty@.discussions.microsoft.com> wrote in message
news:B0D87B12-BDCB-488E-89C2-F5D713DFE80E@.microsoft.com...
>I need to be able to construct a SELECT statement in a stored procedure and
> concatenate a CSV list of values passed as an input parm to the stored
> procedure. How can I do this without declaring a variable for the Select,
> then executing it. I need to use this with Reporting Services so unless
> the
> Select fields are available in the query, RS won't work.|||See
http://www.sommarskog.se/arrays-in-sql.html
http://www.users.drew.edu/skass/SQL...unction.sql.txt
The outline is like this:
select stuff
from T
join ListToTableFunction(@.myList) as Items
on Items.Item = T.Item
or with the logic of ListToTableFunction directly
in the non-dynamic query. <>
Steve Kass
Drew University
Kitty wrote:

>I need to be able to construct a SELECT statement in a stored procedure and
>concatenate a CSV list of values passed as an input parm to the stored
>procedure. How can I do this without declaring a variable for the Select,
>then executing it. I need to use this with Reporting Services so unless th
e
>Select fields are available in the query, RS won't work.
>|||>> I need to be able to construct a SELECT statement in a stored
procedure and concatenate a CSV list of values passed as an input parm
to the stored procedure. <<
No, you need to learn about coupling, cohesion and the basics of
software engineering. You are supposed to know what you are doing
before you do it. The idea of doing code on the fly is a violation of
everything you should have been taught in a freshman CS course.

how do 2 active statements for a connection ?

SQL Server 2000
ODBC driver
The second statement receive the error: "Connection is busy with result for
another hstmt"
There are ODBC driver for SQL Server that suport server side cursor such as
to be possible 2 active statements for a connection ?You can only have one active statement per connection.
Mike O.
"Pluta Gabriel" <Gabriel.Pluta@.deuromedia.ro> wrote in message
news:O6y$oeF3DHA.3216@.TK2MSFTNGP11.phx.gbl...

quote:

---

> SQL Server 2000
> ODBC driver
>
> The second statement receive the error: "Connection is busy with result

---

for

quote:

---

> another hstmt"
> There are ODBC driver for SQL Server that suport server side cursor such

---

as

quote:

---

> to be possible 2 active statements for a connection ?
>

---

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.
I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegro ups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>
|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> I can't make head or tail of what you are trying to do here (mostly because
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, it
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired results.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegro ups.com...

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> I can't make head or tail of what you are trying to do here (mostly becaus
e
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, i
t
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired result
s.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...|||On 3 Jan 2007 06:50:11 -0800, Loganx80 wrote:

>OK,
>Sorry that my example isn't the easiest to understand. I was actually
>just trying to make it more readable. If it's true that the CASE
>expression cannot return more than one value (such as an IN list), then
>I may need to take another approach, but let me at least give you a
>better example.
>Let's say I have a table with products in it. Each product has a
>category #.
>Categories:
>1 Shoes
>2 Pants
>3 Shirts
>4 TVs
>5 DVD Players
>6 Xboxes
>Now I want to do a select statement based upon a category type passed
>into the procedure @.CategoryType. When @.CategoryType is "electronics",
>I want to return all products with category numbers 4, 5, or 6.
>Likewise, if "clothing" is passed in, I want to return all products
>with 1, 2, 3.
>SELECT * FROM Products WHERE CategoryID IN
> (CASE @.CategoryType
> WHEN "electronics" THEN (4,5,6)
> WHEN "clothing" THEN (1,2,3)
> END)
>
>Hopefully, this makes more sense.
Hi Matthew,
Best way to do this is to add a categories table with a CategoryID and a
CategoryType column. That way, you can add products (and categories!)
without having to change any queries. I have always hated hard-coded
magic values.
But if you do want to hard-code the values, try something like this
SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM Products
WHERE (@.CategoryType = 'electronics' AND CategoryID IN (4, 5, 6))
OR (@.CategoryType = 'clothing' AND CategoryID IN (1, 2, 3));
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:
> I can't make head or tail of what you are trying to do here (mostly because
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, it
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired results.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
> >I am trying to run a query that filters using an IN statment, but the
> > IN statement needs to be dynamically generated based upon some
> > criteria. Here is my example:
> >
> > DECLARE @.tempTable TABLE
> > (
> > letter char(1),
> > id int
> > )
> >
> > INSERT INTO @.tempTable VALUES ('A', 1)
> > INSERT INTO @.tempTable VALUES ('B', 2)
> > INSERT INTO @.tempTable VALUES ('C', 3)
> >
> > SELECT * FROM @.tempTable WHERE letter IN
> > (Case 2
> > WHEN 1 THEN ('A')
> > WHEN 2 THEN ('B', 'C')
> > END)
> >
> >
> > If you try to run this code, it gives an error on the line that
> > contains the 'B', 'C' (incorrect syntax near ',').
> >
> > If you remove the C and just leave the two lines, one with A, one with
> > B, then it will work as expected.
> >|||On 3 Jan 2007 06:50:11 -0800, Loganx80 wrote:
>OK,
>Sorry that my example isn't the easiest to understand. I was actually
>just trying to make it more readable. If it's true that the CASE
>expression cannot return more than one value (such as an IN list), then
>I may need to take another approach, but let me at least give you a
>better example.
>Let's say I have a table with products in it. Each product has a
>category #.
>Categories:
>1 Shoes
>2 Pants
>3 Shirts
>4 TVs
>5 DVD Players
>6 Xboxes
>Now I want to do a select statement based upon a category type passed
>into the procedure @.CategoryType. When @.CategoryType is "electronics",
>I want to return all products with category numbers 4, 5, or 6.
>Likewise, if "clothing" is passed in, I want to return all products
>with 1, 2, 3.
>SELECT * FROM Products WHERE CategoryID IN
> (CASE @.CategoryType
> WHEN "electronics" THEN (4,5,6)
> WHEN "clothing" THEN (1,2,3)
> END)
>
>Hopefully, this makes more sense.
Hi Matthew,
Best way to do this is to add a categories table with a CategoryID and a
CategoryType column. That way, you can add products (and categories!)
without having to change any queries. I have always hated hard-coded
magic values.
But if you do want to hard-code the values, try something like this
SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM Products
WHERE (@.CategoryType = 'electronics' AND CategoryID IN (4, 5, 6))
OR (@.CategoryType = 'clothing' AND CategoryID IN (1, 2, 3));
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How can wrap a case statement around us

I would like to maybe wrap a case statement around the following so that I
can set any results that come back as NULL to 0 (the performance of updating
afterwards is horrible)
update STAGE_PHX_FACT_POLICY
set STAGE_PHX_FACT_POLICY.fire_fee_if = whdata1.dbo.premium_detail.fee,
STAGE_PHX_FACT_POLICY.fire_fee_written =
whdata1.dbo.premium_detail.billed_premium
from whdata1.dbo.premium_detail
inner join whdata1.dbo.lob_xref
on whdata1.dbo.premium_detail.premium_lob = whdata1.dbo.lob_xref.premium_lob
where whdata1.dbo.lob_xref.a_lob = '6'
and whdata1.dbo.premium_detail.policy_number =
STAGE_PHX_FACT_POLICY.policy_number
and whdata1.dbo.premium_detail.policy_date_time =
STAGE_PHX_FACT_POLICY.policy_date_time
Thanks you!>> ...around the following so that I can set any results that come back as
Lookup COALESCE or ISNULL function in SQL Server Books Online
Anith|||"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:3B52A763-5C4B-4030-ACDF-7661406D5149@.microsoft.com...
>I would like to maybe wrap a case statement around the following so that I
> can set any results that come back as NULL to 0 (the performance of
> updating
> afterwards is horrible)
>
> update STAGE_PHX_FACT_POLICY
> set STAGE_PHX_FACT_POLICY.fire_fee_if = whdata1.dbo.premium_detail.fee,
> STAGE_PHX_FACT_POLICY.fire_fee_written =
> whdata1.dbo.premium_detail.billed_premium
> from whdata1.dbo.premium_detail
> inner join whdata1.dbo.lob_xref
> on whdata1.dbo.premium_detail.premium_lob =
> whdata1.dbo.lob_xref.premium_lob
> where whdata1.dbo.lob_xref.a_lob = '6'
> and whdata1.dbo.premium_detail.policy_number =
> STAGE_PHX_FACT_POLICY.policy_number
> and whdata1.dbo.premium_detail.policy_date_time =
> STAGE_PHX_FACT_POLICY.policy_date_time
You can use COALESCE or ISNULL to do this in a much simpler way. As you
didn't specify what needed updating, I'll take a guess that it's both
values:
update STAGE_PHX_FACT_POLICY
set STAGE_PHX_FACT_POLICY.fire_fee_if =
COALESCE(whdata1.dbo.premium_detail.fee,0) ,
STAGE_PHX_FACT_POLICY.fire_fee_written =
COALESCE(whdata1.dbo.premium_detail.billed_premium,0)
from whdata1.dbo.premium_detail
inner join whdata1.dbo.lob_xref
on whdata1.dbo.premium_detail.premium_lob = whdata1.dbo.lob_xref.premium_lob
where whdata1.dbo.lob_xref.a_lob = '6'
and whdata1.dbo.premium_detail.policy_number =
STAGE_PHX_FACT_POLICY.policy_number
and whdata1.dbo.premium_detail.policy_date_time =
STAGE_PHX_FACT_POLICY.policy_date_time
Dan

How can we format data in single MDX expression rather than MDX statement?

Hi, experts,

Thanks for your kind attention.

How can we format data by a single MDX expression rather than MDX statement? (e.g. I want to format measure member A with 2 decimal places. How can we achieve this in a single MDX expression?, not in complex MDX statement).

Hope it is clear for your help.

Thanks and I am looking forward to hearing from you shortly.

With kind regards,

Yours sincerely,

Hi,

Not to sure what you mean by expression rather than MDX Statement.

In a query it might look like:

with member [measures].[Percentage Of Region Total] as

[Measures].[Charge Out]/

([Measures].[Charge Out],[Campaign].[Campaign Region Hierarchy].[All Campaign Regions]),

format_string ='#.##%'

In the calculation script another might look like

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Rev Variance]

AS ([Measures].[Sales Revenue])-([Measures].[Sales Revenue],[Revenue Recognition Date].[Financial Year].currentmember.prevmember),

FORMAT_STRING = "￡#,#",

NON_EMPTY_BEHAVIOR = { [Sales Revenue] },

VISIBLE = 1;

Hope that helps

Matt

|||

Or in the MDX Script of the cube with

Code Snippet

format_string([Measures].[Discount])="#,#.00";

HANNES

|||

Hi, Thanks very much, Matt and HANNES.

With kindest regards,

Yours sincerely,

How can I write this SQL statement when the string value is a variable

Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, MattJust declare @.StockSymbol as nchar or nvarchar and you can simply use

SET @.AlertSymbol = @.StockSymbol

The N in N'foo' tells SQL Server that 'foo' is in unicode. The same applies to all n<bar> datatypes.|||Originally posted by mt404
Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, Matt

Nchar/varchar has higher precedence than char/varchar. Hence, an implicit conversion should take care of this for you.

e.g.

declare @.AlertSymbol nvarchar(10),
@.StockSymbol varchar(10)

set @.StockSymbol='MSFT'

set @.AlertSymbol=@.StockSymbol

--sql2k
select sql_variant_property(@.AlertSymbol,'BaseType'), @.AlertSymbol|||Thaks to both of you for helping me out and teaching me what the N'foo' actually meant.

how can i use The "Use Database" statement >>>

the question is how to use use database statement if

the name of the database contain spaces

??

please help me

Thanks

CREATE DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
GO
USE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
SELECT * FROM
INFORMATION_SCHEMA.TABLES
USE Master
GO
DROP DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de|||

Frank Kalis wrote:

CREATE DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
GO
USE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
SELECT * FROM
INFORMATION_SCHEMA.TABLES
USE Master
GO
DROP DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de

thats too funny! I picture bart writing that on the blackboard in a future episode of The Simpsons

|||Unfortunately you can't add images here in the fora, but I believe you mean this one
http://www.insidesql.de/images/stories/google_bart.gif

But I think, this one's also pretty cool
http://www.insidesql.de/images/6thsense.jpg
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de|||

why all this my friend ?!!

don't exaggerate

i'm tried to write the following statement before :

Insert Into [My DB].MyTable(No) values 6

and the compiler refuse the statement ..

so i send for help

|||

Insert Into [My DB].MyTable(No) values (6)

|||If I offended you, please accept my apologies!
My links were intended only to be fun, nothing more. They are neither related to you nor your question.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de

how can i use The "Use Database" statement >>>

the question is how to use use database statement if

the name of the database contain spaces

??

please help me

Thanks

CREATE DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
GO
USE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
SELECT * FROM
INFORMATION_SCHEMA.TABLES
USE Master
GO
DROP DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||

Frank Kalis wrote:

CREATE DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
GO
USE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
SELECT * FROM
INFORMATION_SCHEMA.TABLES
USE Master
GO
DROP DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de

thats too funny! I picture bart writing that on the blackboard in a future episode of The Simpsons

|||Unfortunately you can't add images here in the fora, but I believe you mean this one
http://www.insidesql.de/images/stories/google_bart.gif

But I think, this one's also pretty cool
http://www.insidesql.de/images/6thsense.jpg
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||

why all this my friend ?!!

don't exaggerate

i'm tried to write the following statement before :

Insert Into [My DB].MyTable(No) values 6

and the compiler refuse the statement ..

so i send for help

|||

Insert Into [My DB].MyTable(No) values (6)

|||If I offended you, please accept my apologies!
My links were intended only to be fun, nothing more. They are neither related to you nor your question.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de

how can i use The "Use Database" statement

the question is how to use use database statement if

the name of the database contain spaces

??

please help me

Thanks

CREATE DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
GO
USE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
SELECT * FROM
INFORMATION_SCHEMA.TABLES
USE Master
GO
DROP DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||

Frank Kalis wrote:

CREATE DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
GO
USE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
SELECT * FROM
INFORMATION_SCHEMA.TABLES
USE Master
GO
DROP DATABASE [I SHALL NOT USE BLANKS IN ANY OBJECT NAME]
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de

thats too funny! I picture bart writing that on the blackboard in a future episode of The Simpsons

|||Unfortunately you can't add images here in the fora, but I believe you mean this one
http://www.insidesql.de/images/stories/google_bart.gif

But I think, this one's also pretty cool
http://www.insidesql.de/images/6thsense.jpg
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||

why all this my friend ?!!

don't exaggerate

i'm tried to write the following statement before :

Insert Into [My DB].MyTable(No) values 6

and the compiler refuse the statement ..

so i send for help

|||

Insert Into [My DB].MyTable(No) values (6)

|||If I offended you, please accept my apologies!
My links were intended only to be fun, nothing more. They are neither related to you nor your question.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de

how can i use the " use " statement with database-name contains spaces in sql2000

example :

i cann't write :

use [my db]

what can i do with this problem ?

That works for me:

use [master]

CREATE DATABASE [Some DB]

USE [Some DB]

SELECT DB_NAME()

-

Some DB

(1 row(s) affected)

USE MASTER

DROP DATABASE [Some DB]

What error are you getting back ? Regardless of the possibiliy to put a space in the name, this is NOT recommended. It might work in the most cases but you will run in many cases where this isn′t supported (liek third party vendors)

HTH, Jens Suessmeyer.

How can I use stored procedure's recordset output into simple SELECT statement?

Hi All
I have one stored procedure called MY_SP that returns a recordset. I would
like to use this recordset output into my SELECT statement like this:
SELECT * FROM (EXEC MY_SP '1', '2') RS
The above blurs up with syntax error at EXEC. Is there a way to achieve
this? (Note that MY_SP takes few parameters)
Thanks in advance.
NayanI don't understand why don't you just call EXEC MY_SP '1', '2'?
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have one stored procedure called MY_SP that returns a recordset. I
> would like to use this recordset output into my SELECT statement like
> this:
> SELECT * FROM (EXEC MY_SP '1', '2') RS
> The above blurs up with syntax error at EXEC. Is there a way to achieve
> this? (Note that MY_SP takes few parameters)
> Thanks in advance.
> Nayan
>|||thanks for asking
The example I have included is for the purpose of getting my problem across
to the audience. I agree with you that if I need to do something as simple
as quoted, I would rather do it your way. I'm actually trying to further
create a complex query using SELECT that will require data from this SP.
I hope I have made my question little more clearer.
TIA
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>I don't understand why don't you just call EXEC MY_SP '1', '2'?
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
>|||Use Temp table to store the resultset from MY_SP is one option.
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
> thanks for asking
> The example I have included is for the purpose of getting my problem
> across to the audience. I agree with you that if I need to do something
> as simple as quoted, I would rather do it your way. I'm actually trying
> to further create a complex query using SELECT that will require data from
> this SP.
> I hope I have made my question little more clearer.
> TIA
> Nayan
> "Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
> news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>|||Thanks Perayu for your quick response.
The temp table option will work fine but what if I dont have permission to
modify the said SP? In that case, what would be my options?
thanks again for your ideas.
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:OfZwUV6RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Use Temp table to store the resultset from MY_SP is one option.
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
>|||On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:

>Thanks Perayu for your quick response.
>The temp table option will work fine but what if I dont have permission to
>modify the said SP? In that case, what would be my options?
Hi Nayan,
You don't have to modify the SP. You can insert the results of the SP in
a tem table, then use that in your later queries:
CREATE TABLE #Reults
(Col1 some_datetype NOT NULL,
..)
INSERT INTO #Results (Col1, ...)
EXEC EXEC MY_SP '1', '2'
Hugo Kornelis, SQL Server MVP|||In light of your other posts, rewrite the stored procedure as a table
returning function and then you can easily use it in the FROM part of a
SELECT statement.|||Hi Hugo.
Thanks for replying.
I already did this earlier but wanted know if there is a simpler way that
can allow me to pipe-in the resultset from an SP without first creating the
table.
thanks again.
Nayan
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:r7le129b97pr04pcafqo0boti13qba0945@.
4ax.com...
> On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:
>
> Hi Nayan,
> You don't have to modify the SP. You can insert the results of the SP in
> a tem table, then use that in your later queries:
> CREATE TABLE #Reults
> (Col1 some_datetype NOT NULL,
> ...)
> INSERT INTO #Results (Col1, ...)
> EXEC EXEC MY_SP '1', '2'
> --
> Hugo Kornelis, SQL Server MVP|||Hi JeffB
I understand your solution. What I'm looking at is how can I directly call
the stored procedure in the FROM part of the SELECT statement. I hope the
answer to this is not "NO" - cant use a stored procedure in the FROM part!
Thanks for the reply
Nayan
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142374607.563351.82890@.i40g2000cwc.googlegroups.com...
> In light of your other posts, rewrite the stored procedure as a table
> returning function and then you can easily use it in the FROM part of a
> SELECT statement.
>|||I don't believe that you can. This is what a function that returns a
table is for.