Use Variables Or Multiple SELECT's?

Jun 6, 2000

Stored procedure retrieves a single row from a single table... Based on the specific values in 4 different columns, different branch actions are taken using 4 nested IF statements.

The question is, what is more efficient: storing column values in 4 variables and then evaluting each of them, or executing the same query 4 times?

Scenario A:

DECLARE @var1 char(20), @var2 char(20), @var3 char(20), @var4 char(20)
SELECT @var1 = col1, @var2 = col2, @var3 = col3, @var4 = col4
FROM theTable
WHERE rid = 12345
IF @var1 = 1
...
ELSE IF @var2 = 2
...
ELSE IF @var3 = 3
... etc.
---------------

Scenario B:

IF (SELECT col1 FROM theTable WHERE rid = 12345) = 1
...
ELSE IF (SELECT col2 FROM theTable WHERE rid = 12345) = 2
...
ELSE IF (SELECT col3 FROM theTable WHERE rid = 12345) = 3
... etc.
--------

Scenario A or B? Please advise...


TIA,
Alex

View 1 Replies


ADVERTISEMENT

Multiple Variables Assigned To One Select

Apr 6, 2006

Hello,
Is there a way to assign multiple variables to one select statement as in the following example?
DECLARE @FirstName VARCHAR(100)
DECLARE @MiddleName VARCHAR(100)
DECLARE @LastName VARCHAR(100)
@FirstName, @MiddleName, @LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'
 
I don't like having to use one select statement for each variable I need to pull from a query.  This is in reference to a stored procedure.
 
Thank you!
Cody

View 1 Replies View Related

SqlDataReader - Pulling Multiple Values Into Multiple Variables

Mar 29, 2007

Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{    String UserName;    String userId;    String HiveConnectionString;    String Current_Location;    ArrayList Location;    public String Location1;    public String Location2;    public String Location3;    //Int32 x = 0;    private void Page_Load(object sender, EventArgs e)    {        if (User.Identity.IsAuthenticated)        {            UserName = Membership.GetUser().ToString();            userId = Membership.GetUser().ProviderUserKey.ToString();            HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647";            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString))            {                // Map Updates                 MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand(                        "SELECT Location FROM tracker WHERE Location = IsOnline = '1'");                Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                Locationcmd.Connection = conn;                conn.Open();                MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader();                 while (LocationReader.Read())                {                        Location1 = LocationReader.GetString(0);                        //Location2 = LocationReader.GetString(1); // This does not work..                }                                LocationReader.Close();                conn.Close();                // IP Display                MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand(                        "SELECT UserName FROM tracker WHERE PKID = ?PKID");                Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                Checkcmd.Connection = conn;                conn.Open();                object UserExists = Checkcmd.ExecuteScalar();                conn.Close();                if(UserExists == null)                {                    MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand(                        "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)");                                        Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress;                    Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName;                    Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                    Insertcmd.Connection = conn;                    conn.Open();                    Insertcmd.ExecuteNonQuery();                    conn.Close();                }                else                {                    MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand(                        "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID");                    Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress;                    Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName;                    Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                    Updatecmd.Connection = conn;                    conn.Open();                    Updatecmd.ExecuteNonQuery();                    conn.Close();                }            }        }    } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone! 

View 1 Replies View Related

Insert Into Multiple Variables From One SQL Call

May 30, 2008

I was wondered what the most efficient way to do the following in MS SQL (2000, 2005, and above):
 Psudo Code:
DECLARE @var1, @var2, @var3 VARCHAR(100)
SET @var1, @var2, @var3 = (SELECT var1, var2, var3 FROM Table WHERE [ID] = @someID) <-- Returns only one row
Right now I'm making a call for every variable. I should play around with temp tables but seems like allot of overhead code just to get 3 values. I'm thinking there's a simple way to do this.
 Thanks for any ideas

View 3 Replies View Related

How To SET Multiple Variables From One Table Record?

Apr 18, 2005

It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.
Currently I'll do something like this:
DECLARE @var1 intSET @var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @somekey)DECLARE @var2 nvarchar(20)SET @var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @somekey)
Of course, I'd rather just have to query "table1" just once to assign my variables.
What obvious bit of T-SQL am I missing?
Thank you in advance.
 

View 2 Replies View Related

ScriptComponent With Multiple ReadOnly Variables

Dec 11, 2006

I'm having trouble with a script component in which I'm trying to use two ReadOnlyVariables. If I use only one of the two variables, everything works without issue. If I use both of the variables (as part of a comma-delimited list) I get the following:

The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.

I don't believe the variables themselves are the problem. Both are scoped to the package level and I can use either of them if I have it as the only variable. Seems bug-like, but thought I'd get some ideas before pursuing that route.

View 11 Replies View Related

Variables In An SQL Select

May 14, 2007

Pretty simple question and I see it's been asked a few times with no answers so I thought I'd try again. :)
Is there a way to put a variable into an SQL connection string? Like: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:BGHelpdeskConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM MyTable WHERE name=" & response.write(MyName)
.... etc 

View 7 Replies View Related

SELECT INTO And Variables

Aug 15, 2007

Hi.

I was wondering if I could use SELECT INTO to put my data into variables.




I'm coding in VB using Visual Web Developer.

Basically, I want to do this:

SELECT data3, data4 INTO variable1, variable 2 FROM table WHERE data1= textbox1.text, data2 = textbox2.text

I am pulling data based on a selection of name (data1) and date (data2).


Thanks!

View 4 Replies View Related

Is It Possible To Dynamically Create Multiple Variables In A Package?

May 21, 2008

I'd like to be able to call different packages from a control flow. These packages will have different requirements for parameters therefore I'd like to create them dynamically.

Is this possible? Can I do it using a script task?

Thanks in advance.
Ben

View 10 Replies View Related

Loading Multiple Variables From Within The Same Execute Sql Task

Aug 28, 2007

Hi All,

I was wondering if it is possible to assign values to multiple variables from within the same execute sql task, ie I want to use only one execute sql task and have multiple T-SQL statements within it and then assign the results of these sql statemenst as values to multiple variable.

Typically I would declare variables var1 , var2 and var3 , then can I just add one execute sql task and have mutiple sql statements within it? something like this

select max(id) from table1

select max(id) from table2

select max(id) from table3

Thanks

View 3 Replies View Related

Using Variables In A SELECT Query

Oct 3, 2007

Hi I'm a n00b at ASP.Net and C# but the company I work for requires me to know this and develop their website using these languages.
Anyway I've been doing pretty good so far I got the page looking as it should etc and databases all set up, now I've hit my first real problem... I am using Visual Studio 2005 and in the Web Developer section. I want to make a SELECT query using the <asp:LoginName> control as the where clause.
e.g. if admin logs in there is a welcome message : Welcome Admin now I want to use the 'Admin' <asp:LoginName> as the SELECT in my query i.e. SELECT * FROM tbla WHERE ([fldCustomerCode] =  'LoginName' )
I was just really wondering if that is in the .aspx file or the aspx.cs code behind file?
Thanks for any help given

View 4 Replies View Related

Select With Cascading Variables

Nov 14, 2007



Is there a way to write an insert where the values you're inserting depend on another value in the row. Here is an example of pseudo-code that hopefully demonstrates what I'm tryihg to do:


declare @k1 varchar(100)
declare @k2 varchar(100)
declare @k3 varchar(100)


insert into tbl2
select

field1,
field2,
field3,
@k1=(select sm from tbl3 where mm=field1),
@k2=(select sm from tbl3 where mm=@k1),
@k3=(select sm from tbl3 where mm=@k2)
from

tbl1
where

blah= blahblah and
bldfddf = dfaljk



My main focus is using the variables @k1 - @k2 in the where part of subsequent subqueries in the select statement. I haven't been able to get this to work, but I thought maybe something like it would work. I'd like to do this in one statement rather than using a cursor or loop. I'm also trying to avoid functions. Any help would be appreciated.

View 9 Replies View Related

Cursor Select And Variables

Oct 23, 2006

I have problems to place my variable into the select statement.

DECLARE @DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @DB_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
print @DB_NAME; --works fine

Select NAME, FILEDIRECTORY FROM @DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\%');

FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor

GO

How could i use a variable like @DB_Name in my select ?

View 1 Replies View Related

Retrieve Multiple Variables From Stored Procedure (SQLHelper)

Jun 22, 2004

Hi all,

I am using SQLHelper to run a Stored Procedure.
The Stored Procedure returns 3 variables:

ie:

SELECT @Hits = COUNT(DISTINCT StatID) FROM Stats

...etc...

The SP is currently called as below:

SqlParameter[] sqlParams = new SqlParameter[]
{
new SqlParameter("@FromDate", Request["FromDate"]),
new SqlParameter("@ToDate", Request["ToDate"]),
};


My question is this: How do I retrieve these variables?

I know I need to declare a new SqlParameter and change it's Direction to Output but I am unsure of the exact syntax required to do this.

Thanks in advance,

Pete

View 5 Replies View Related

SQL Server 2012 :: Putting Multiple Values In The Variables?

Jan 28, 2015

how can i put multiple values in the variables.

for eg:

Declare @w_man as varchar
set @w_man = ('julial','BEVERLEYB', 'Lucy') and few more names.

I am getting syntax error(,)

View 5 Replies View Related

Assigning Values To Multiple Variables (via Subqueries) For Use In An Update

Jul 20, 2005

Hi, figured out where I was going wrong in my post just prior, but isthere ANY way I can assign several variables to then use them in anUpdate statement, for example (this does not work):ALTER PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50))ASDECLARE @varCust VARCHAR(50)SELECT @varCust = (SELECT Customer FROM tblWorkOrdersWHERE WorkOrder=@varWO)DECLARE @varAssy VARCHAR(50)SELECT @varAssy=(SELECT Assy FROM tblWorkOrdersWHERE WorkOrder=@varWO)UPDATE statement here using declared variables...I can set one @variable but not multiple. Any clues? kinda new tothis.Thanks,Kathy

View 2 Replies View Related

Using String Variables In SQL Select Statements

Apr 13, 2008

Hi
I am trying to include a string variable in a Select Statement. 
My problem is that when I code with the user name hard coated in the SQL Statement it works fine (see below:)
cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = 'jdoe' " But when I try to use the String variable I get an error (See below):
cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = " & StrUserName
I know there must be something wrong with my syntax ??
Thanks
Jackson

View 5 Replies View Related

Problem With Variables In Select Statement

Oct 22, 2001

I am trying to do a proof of concept on a simple sql statement, but it doesn't work. Am I doing it wrong, or is this not possible....

Table: tblItems
field: account varchar(20)

In Query Analyzer:

DECLARE @acct varchar(20)
SET @acct = '457760,123456'
SELECT account from tblaccount where account in (@acct)

Result:
0 Rows


Any thoughts?

Thanks,
Brian

Expected
457760

View 2 Replies View Related

Using Variables To Select Column Names

Oct 20, 2006

Hi

I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example

eg

DECLARE @column
DECLARE @value
SET @column = 'col1'
SET @value = 'bloggs'

Select * FROM table1 WHERE @column = @value

It keeps returning no results even though i've tried

Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results

I realise its the column which is not being selected, but there must be a way by using a variable?

thanks

View 2 Replies View Related

Seting 2 Variables From 1 Select Statement

Oct 31, 2007

is it possible to assign the results of the qry to the 2 variables?

Declare @a Integer
Declare @b Integer

SELECT A, B from myTable

View 1 Replies View Related

Please Help To Assign Multiple Results Into Individual Variables, Stored Procedure

Apr 12, 2008

I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line
(@Tester1       integer,@Tester2        integer,@Tester3    integer,@Tester4    integer,@Tester5    integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen:
@Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.

View 2 Replies View Related

Assigning Results Of A Select Query To Variables...

Jul 13, 2004

Hi,

I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.

So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like
"select height, width, depth from products where id=@idinput"

OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?

This is what I've got so far...

[code]
cmdSelect = New SqlCommand( "GetProd", connstr )
cmdSelect.CommandType = CommandType.StoredProcedure
dbcon.Open()

dbcon.Close()
[/code]

The main prob is just what to connect this record to in order to access the individual fields.

Thx :)

View 2 Replies View Related

Send Mail Using Variables From Select Statement

Jan 5, 2007

I'm getting the error "No recepient is specified".

I have set up a dataflow from a select statement into a record set, then have that dataflow point to a ForEach group with a mail task in it. I have set up variables for the username and subjectline. So in the mail task I have no value in the To: line because I specify an "http://www.sqlis.com/59.aspx">http://www.sqlis.com/59.aspx exactly.

I couldn't figure out how to included screen shots.

Any ideas?

View 4 Replies View Related

Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?

Dec 4, 2007

I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks

View 7 Replies View Related

Integration Services :: Assign Variables To Multiple Table Results From Stored Procedure

Sep 21, 2015

If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?

View 6 Replies View Related

SQL Server 2012 :: Behavior Of Brackets In Select Clause When Declaring Variables?

Oct 11, 2014

I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'

Below is Query 1:

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar

Out put of this query is 'Noname'

BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar

View 4 Replies View Related

Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

Sep 4, 2006

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

João





View 8 Replies View Related

How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

Jan 24, 2006

Hi,

I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"

'



thanks,



Guangming

View 2 Replies View Related

Multiple Select In One Sp

Oct 25, 2007

 I was wondering if it's possible to have a stored procedure that has
two(or more) select statements which you can combine as a single result set.I am not able to use union as my select returns different number of columns.My selects are like this,This example is using only two table but i can have more then 2 tables in situation..ELECT     SUM(col1) AS sumcol1FROM         tbl1WHERE     (ID = @para1') SELECT     SUM(col1) AS sumcol1, SUM(col2) AS sumcol2, SUM(col3)                       AS sumcol3, SUM(col4) AS sumcol1FROM         tbl2WHERE     (id = @para1).thanks  

View 6 Replies View Related

Multiple Select

Dec 15, 2006

Hi,

In my report i have a paremeter which is a dropdown list showing alla available values.Now i want to select multiple values.The parameter is not multi valued.

From the following link:

http://www.ssw.com.au/ssw/Standards/BetterSoftwareSuggestions/ReportingServices.aspx#RichTexbox

Multiple select in Parameters Is fixed in Sqlserver reporting services 2005.

Iam using 2005 version,But i can't able to select multiple values by holding Ctrl key.How to achieve this.If we able to select the multiple values,In wgich format the selected value is passed to the Stored Procedure,It is same the way as selecting multiple values from Multi valued Parameter.

Please help me.

Thanks in advance

View 4 Replies View Related

Select Multiple Tables

Jul 9, 2007

Hi there,I want to select records from 3 tables. In SQL Server 2005, I'm using of "For XML" like this:Select *, (Select * From dbo.PageModules Where (PageId = 1) For Xml Auto) As Modules, (Select * From dbo.PageRoles Where (PageId = 1) For Xml Auto) As Roles From dbo.PagesThat works fine in SQL 2005 but not in SQL 2000, Because SQL 2000 does not support nested "FOR XML".Is there any way for selecting records from multiple tables by a query?Thanks in advance 

View 4 Replies View Related

USING MULTIPLE SELECT STATEMENTS

Apr 23, 2008

how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS'
 Code1....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE1')
 
Code2.....
 
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE2')
 
 thanks in advance

View 10 Replies View Related

Multiple Datatable Select In C# And Sql?

May 10, 2008

Hello.
I have 2 diffrent tables in the database, is it possible to select both of those back to c# on the same time?
I DONT want to combine the tables, just select them into 2 diffrent datatables in one select statement?
At the moment, i have something like:
function XXOpenDBCon();mCom.Parameters.Clear();mCom.CommandText = "Stuff_GetMovieCategories";myAdap = new SqlDataAdapter();myTable = new DataTable();myAdap.SelectCommand = mCom;myAdap.Fill(myTable);CloseDBCon();return myTable;
Can i ask the database to perhaos retrieve Stuff_GetMovieCategories and on the same time retrieve Stuff_MostViewedMovies?
So the SP does 2 select statements and returns them, i get in the function above 2 datatables with the information, one datatable with Categories and one table with MostViewedMovies.
Is this possible?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved