MS SQL Server :: Use A Variable Along With The FROM Clause In SELECT Statement

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list

I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary

How do I combine the select statement with the where clause?

Help me...I need help urgently

View Replies !

SQL Select Command With Where Clause Variable Based On ASP.NET TextBox
All,

Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value.
ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid
This is all done in Page_Load event backend code.
string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";

View Replies !

IF Statement In SELECT WHERE Clause
Hi Everyone,

I have the following stored procedure, I would like to use
IF statement or something of the sort in the where clause i.e.
The last line in the SP is: AND (category.categoryID = @categoryID),
I only want to check this, if @categoryID is not = 12.
So can I do something like this:

IF @categoryID <> 12
AND (category.categoryID = @categoryID)

STORED PROCEDURE:

CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS

SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = @categoryID)

View Replies !

Gridview Select Statement With User.identity.name In The Where Clause
Hello everyone,
I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches.
So far i have tried:
SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' "
with no success.
Any help is appreciated

View Replies !

Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<SelectParameters>
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
</SelectParameters>
</asp:SqlDataSource>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:

Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Else
Label1.Text = Item.Value + ","
End If
End If
Next
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here? Thanks!

View Replies !

Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:

SELECT
l.loanid,
p.investorid,
l.duedate,
case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate,
pc.interestrate
FROM loan l
inner join participation p on p.loanid = l.loanid
inner join paymentchange pc on pc.loanid = l.loanid
where p.investorid = '12345' and RateDueDate is not null
order by l.loanid, pc.duedate

I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.

Thanks!

View Replies !

Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please
I am trying to set a vaiable from a select statement

DECLARE @VALUE_KEEP NVARCHAR(120),

@COLUMN_NAME NVARCHAR(120)

SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')

SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)

PRINT @VALUE_KEEP

PRINT @COLUMN_NAME

RESULTS

-------------------------------------------------------------------------------------------

FirstName <-----------@VALUE_KEEP

FirstName <-----------@COLUMN_NAME

SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent

How do I make this select statement work using the @COLUMN_NAME variable?

Any help greatly appreciated!

View Replies !

Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings,

I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.

How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.

ALTER PROCEDURE [dbo].[MyProcedure]

AS

BEGIN

SELECT DISTINCT A.Name, A.index

FROM
...
...
ORDER BY A.[Index], A.Name ASC

END

ALTER PROCEDURE [dbo].[MyProcedure]

AS

BEGIN

SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]

END

Thanks

View Replies !

Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile...

Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.

However the select statement itself runs perfectly well as a query, no errors.

The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.

What gives with this?

Thanks in advance
R.

The code:

Code Snippet

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
    DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author:        -----
-- Create date:
-- Description:    inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
    -- Add the parameters for the stored procedure here
    @importedReportID int,
    @authCode varchar(12)
AS
BEGIN
    DECLARE @errmsg VARCHAR(80);

--    SET NOCOUNT ON added to prevent extra result sets from
--    interfering with SELECT statements.
    SET NOCOUNT ON;

    --IF (@authCode <> 'TX-TEC')
    --BEGIN
     -- SET @errmsg = 'Unsupported reporting format:' + @authCode
    -- RAISERROR(@errmsg, 11, 1);
    --END

    DECLARE srcRecsCursor CURSOR LOCAL
    FOR    (SELECT
           ImportedRecordID
        ,ImportedReportID
        ,AuthorityCode
        ,[ID]
        ,[Field1] AS RecordType
        ,[Field2] AS FormType
        ,[Field3] AS ItemID
        ,[Field4] AS EntityCode
        ,[Field5] AS LastName
        ,[Field6] AS FirstMiddleNames
        ,[Field7] AS Title
        ,[Field8] AS Suffix
        ,[Field9] AS AddressLine1
        ,[Field10] AS AddressLine2
        ,[Field11] AS City
        ,[Field12] AS [State]
        ,[Field13] AS ZipFull
        ,[Field14] AS OutOfStatePAC
        ,[Field15] AS FecID
        ,[Field16] AS Date
        ,[Field17] AS Amount
        ,[Field18] AS [Description]
        ,[Field19] AS Employer
        ,[Field20] AS Occupation
        ,[Field21] AS AttorneyJob
        ,[Field22] AS SpouseEmployer
        ,[Field23] As ChildParentEmployer1
        ,[Field24] AS ChildParentEmployer2
        ,[Field25] AS InKindTravel
        ,[Field26] AS TravellerLastName
        ,[Field27] AS TravellerFirstMiddleNames
        ,[Field28] AS TravellerTitle
        ,[Field29] AS TravellerSuffix
        ,[Field30] AS TravelMode
        ,[Field31] As DptCity
        ,[Field32] AS DptDate
        ,[Field33] AS ArvCity
        ,[Field34] AS ArvDate
        ,[Field35] AS TravelPurpose
        ,[Field36] AS TravelRecordBackReference
    FROM ImportedNativeRecords
    WHERE ImportedReportID IS NOT NULL
    AND ReportType IN ('RCPT','PLDG')
    ORDER BY ImportedRecordID -- this should work but gives syntax error!
    );

END

View Replies !

Variable In A Select Statement
Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column.

select @column_name from table

View Replies !

Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter.

I want to do a Select statemant such as:

Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded --
Select name from pubs..sysobjects where xtype = 'u'

but I can't find the right way of using @DBName

View Replies !

Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter, for example-- @DBName varchar(30)

I want to do a Select statemant such as:

Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded --
Select name from pubs..sysobjects where xtype = 'u'

but I can't find the right way of using @DBName

Thanks,
Judith

View Replies !

SQL Select Statement With A 'string' Variable?
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);

View Replies !

Using A Variable For Tablename In Select Statement?
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"?
When I try that is says "Must declare the table variable @Table_Name". Thanks!

View Replies !

Variable For The Table Name In A SELECT Statement.
Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20))
AS
BEGIN TRANSACTION
SET NOCOUNT ON

DECLARE @ProjectNumber int
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SELECT MAX(@ProjectNumber)
FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','');
END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.

View Replies !

Variable Not Holding Value For Select Statement
this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on (((   @UsersMaxID ))) but for some reason it will not work with the next select statement...

can someone make the pain go away and help me here..??

erik..

GOSET ANSI_NULLS ON GO
ALTER PROCEDURE AA
ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200)
SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik'
DECLARE @SQL NVARCHAR(4000) DECLARE @UserID INT DECLARE @UsersMaxID INT DECLARE @MaxID INT
declare @tempResult varchar (1000)
-------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY,
UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr)
Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr
FROM USERS
WHERE ' + @GenericColumn +' = ''' + @GenericValue + ''''
EXEC sp_executesql @SQL
SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29
SELECT * FROM #UsersTempTable

==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE

View Replies !

How Do I Use A Variable To Specify The Column Name In A Select Statement?
How do I use a variable to specify the column name in a select statement?

declare @columnName <type>

set @columnName='ID'

select @columnName from Table1

View Replies !

Put Select Statement In SSIS Variable
Is it possible to add a variable in SSIS like

name of variable: myVar
Scope: Data Flow Task
Data Type: String
Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone])

@[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made

I know I'm doing something wrong with the data type because it's stores the whole select statement as a string

Help

Worf

View Replies !

How To Create A Select Statement With An Increasement Variable?
Example:

Select icount + 1 as icount from table

or

Select counter() as icount from table

The above is wrong ...just a sample to show what I am trying to accomplish.

Is there a function in SQL statement? Thanks.

View Replies !

Return Variable Name As Part Of Select Statement.
hey all,

I have the following query:

ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]

AS
BEGIN

DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)

DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)

SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count

FROM article

JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id

GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name

HAVING article_id = @article_id

END
GO

as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.

I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.

But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.

And i cant add a join, because it would return the amount of rows of the comment...

unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?

Cheers

View Replies !

Getting A Variable That Has A SELECT Statement To Return Results
I have concatenated a long Select Statement and assigned it to a variable. (i.e.)

@a = Select * from foo ---Obviously mine is much more robust

How do I execute @a to actually Select * from foo?

View Replies !

Storing Results Of Select Statement In @variable

I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:

SET @value = select max(price) from product

but this does not work, can someone tell me how I would go
about in storing the results in a variable.

@value is declared as int

Thanks in advance,
Sharp_At_C

View Replies !

How To Assign The SELECT Statement Output To A Local Variable?

In my program i have function that will get one value from Database.
Here i want to assign the output of the sql query to a local variable.
Its like select emp_id into Num from emp where emp_roll=222;
here NUM is local variable which was declared in my program.
Is it correct.?
can anyone please guide me..?

View Replies !

Unable To Create Variable Select Statement In For Each Loop
What I'm trying to do is this;

I have a table with Year , Account and Amount as fields. I want to

SELECT Year, Account, sum(Amount) AS Amt

FROM GLTable

WHERE Year <= varYear

varYear being a variable which is each year from a query

SELECT Distinct Year FROM GLTable

My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.

What I have done is to defined two variables as follows

Name: varYear (this will hold the year)

Scope: Package

Data type: String

Name:vSQL (This will hold a SQL statement using the varYear)

Scope: Package

Data type: String

Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]

I've created a SQL Task as follows

Result set: Full Result Set

Connection Type: OLE DB

SQL Statement: SELECT DISTINCT Year FROM GLTable

Result Name: 0

Variable Name: User::varYear

Next I created a For Each Loop container with the following parameters

Enumerator: Foreach ADO Enumerator

ADO Object source Variable: User::varYear

Enumeration Mode: Rows in First Table

I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows

Data Access Mode: SQL Command from Variable

Variable Name: User::varYear

However this returns a couple of errors "Statement(s) could not be prepared."

and "Incorrect syntax near '='.".

I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.

Any help would be most appreciated.

Regards,

Bill

View Replies !

How To Write A SELECT Statement And Store The Result In A Session Variable
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns

ContactID

View Replies !

Random Selection From Table Variable In Subquery As A Column In Select Statement
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?

SET NOCOUNT ON

Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000

Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)

Set @Counter = 0

while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end

Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'

Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'

Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'

select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type

from @NumericBase

SET NOCOUNT OFF

View Replies !

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View Replies !

Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization....

I have a query that looks like this:

SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)

it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View Replies !

SELECT Then DELETE Versus Extra Clause In SELECT
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'

View Replies !

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples:

1) Rows ordered using textual id rather than numeric id

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
order by
v.id

Result set is ordered as: 1, 11, 2
I expect: 1,2,11

if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
cross join (
  select 1 id
  union select 2 id
  union select 11 id
) u
order by
v.id
    ,u.id

Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)   May 3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb 9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View Replies !

Using A Variable In The FROM Clause
Is it possible to have a variable stand for the table names in a FROM clause for a select statement?

If not, does anyone have any suggestions on how do do this instead?

Thanks for any and all help,

Michelle

View Replies !

Using Variable In Where Clause
I am trying to use a local variable and case statment in a where clause but I keep getting an error. I'm passing an input parameter into a stored procedure and based on the input parameter I want the WHERE clause to reflect.

spOpenClosedList @openclose varchar(5)

DECLARE @result varchar(12)
if @openclose = 'open'
Begin
set @result = 'is null'
end
else
set @result = 'is not null'

SELECT * FROM WorkOrderTbl
WHERE WorkOrderTbl.CompletedDate @result
AND username = "Thomas"

View Replies !

Variable In Where Clause
Hi Gurus,
Can any one help me making thhe following query work without using dynamic sql?

use northwind
declare @fc varchar
set @fc = ' CustomerID '
Select * from dbo.Customers
where @fc like 'a%'

Thanks a lot in adv

Korr

View Replies !

Using Variable In SQL IN Clause
I have a table name emp(empid) having one record as emp1.

Now i am writing

DECLARE @EmpID VARCHAR(8000)
SET @EmpID = '''Emp1'',''Emp2'''

Select * from emp
where empid in(@empid)

But there are no rows returned
can't i use variable in IN Clause. I have to achiev this backend only.

View Replies !

SQL WHERE Clause Passing In Variable
Hi All, Would somebody be able to help me from pulling my hair out!??I have a form with a radiobuttonlist. I would like to change my select statement depending on what radiobutton value is selected.E.g.SELECT * FROM table WHERE <<variable from radiobuttonlist>> LIKE 'Y'So,if radiobutton value selected = 1, it will select * from column A in the dbif radiobutton value select = 2, it will select from column B in the dband so on... Am i attempting to do the impossible?Thanks All,

View Replies !

Order By Clause Using A Variable
I am trying to pass as an input parameter a user selected order by clause, and instead of repeating the SQL statement with a new Order By based on the parameter, I want to set the Order by using this parameter. I can't get it to work.

Here is the statement:

Create Procedure sp_InfoDump
(
@StartDate varchar(12),
@EndDate varchar(12),
@OrderBy varchar(50)
)
As
/* Local variables */
DECLARE @MinDate datetime, @MaxDate datetime

IF @StartDate = 'ALL DATES'
BEGIN
SELECT @MinDate = Min(AccessTime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MinDate = @StartDate
END

IF @EndDate = 'ALL DATES'
BEGIN
SELECT @MaxDate = Max(AccessTime)
FROM tblAudit
END
ELSE
BEGIN
SELECT @MaxDate = @StartDate
END

BEGIN
SELECT tblReports.ReportName, tblReports.ReportCode,
tblAudit.BadAttempts, tblAudit.LogonUser, tblAudit.AccessTime,
tblAudit.RemoteHost, tblAudit.RemoteIdent, tblAudit.ExitTime,
tblAudit.BrowserType, tblAudit.Access_ID, TotalTime=DateDiff(Minute,tblAudit.AccessTime,tblA udit.ExitTime)
FROM tblReports
INNER JOIN
tblReportsAccess ON
tblReports.Report_ID = tblReportsAccess.Report_ID
INNER JOIN
tblAudit ON
tblReportsAccess.Audit_ID = tblAudit.Audit_ID
WHERE tblAudit.AccessTime >= @MinDate AND tblAudit.AccessTime <= @MaxDate
ORDER BY (SELECT 'ColumnName'=ColumnName FROM tblOrderBy WHERE ColumnName = @OrderBy)
END

RETURN

View Replies !

Using A @Variable As An IN Clause Expression
Is there a good way to use a @variable in a Stored procedure to represent the expressions for an IN clause

I.e. I need to accomplish the following:

Create Procedure sp_ABC
@Variable VarChar (1024) AS

Select * From tblAnyTable
Where intTableID IN (@Variable)

Is there a valid way to accomplish this?
The above code presented here does not work, because the VarChar cannot be translated into int.

View Replies !

CONTAIN Clause Not Taking A Variable?
Hi,

I created a full-text catalog on a table and ran a contain clause against it with a declare variable on the search. Example....

DECLARE @chrClause VARCHAR(204)
SET @chrClause = ' "XXXX" '

SELECT ID
FROM Emp
WHERE CONTAINS(Tax_Nam, @chrClause)

I'm getting an error that says...
Line 8: Incorrect syntax near '@chrContainClause'.

Is this a problem i can fix by applying SP3?

View Replies !

Variable To Store String For Where Clause
I want to declare a variable that will serve as my where clause. The variable will be passed in from our website. Does anyone have any information or can guide me in the right direction to do this?

example:

@variable varchar(1000)

SELECT *
FROM table
WHERE @variable

(The @variable would reflect the where clause string)

Thanks!

View Replies !

Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before.

select @var = oldValue

declare testCursor cursor
for select someColumns
from someTable
where someColumn = @var

select @var = newValue

open testCursor

fetch next from testCursor into @someColumns

Thank's in advance.

Mirko.

View Replies !

Stored Procedure With Variable As Only Parameter In Where Clause
Hello,
I want to execute a sproc where the query statement goes something along these lines:
SELECT * FROM myTable WHERE @aVarCharVariable
@aVarCharVariable contains column names and their possible values
How do I achieve this?
Cheers!
/Eskil

View Replies !

Passing Variable To Where Clause In Sql Query From A Grid
Hi everyone I am new to this site I have a major issue I cant figure out seeing how im fairly new to asp, and vb, but i have 5 years php mysql experience.
Im pulling the correct data into a grid. Then i need to make a button or some sort of link that will take the value of one field in the record set and replace it with @transid in the where statement I can enter in the value of transid into form field with that name and it will run the rest of the script correctly, I just cant get past this hurdle. If anyone can help that would be great. I tried to get this to work with java script but then realized thats not possible to transfer varaibles to asp from it.
///javascript
function DisplayReciept(transactionnum)
{
recieptdis = transactionnum;

}

////field in grid
<asp:BoundField htmlEncode=false DataFormatString="<a href=javascript:DisplayReciept{0}>Display</a>" DataField="transid" HeaderText="Show Reciept" SortExpression="transid" />

//////////////query//////////////
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dbsgcConnectionString %>"
SelectCommand="SELECT [fulldata] FROM [data] WHERE ([transid] = @transid)">
<SelectParameters>
<asp:FormParameter FormField="transid" Name="transid" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

View Replies !

How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim

View Replies !

Using A Stored Procedure Variable To Define A Where Clause
Hi all,

I'm trying to build a Where clause in a stored procedure based on the information that is passed into the stored procedure. Because I don't know how many items will be passed into the stored procedure, I'm having to split the string on a specific character and build the Where clause based on how many strings are found.

However, when I try to execute the Where clause it throws an error.

Code Snippet

ALTER PROCEDURE getUsersAddress
    -- Add the parameters for the stored procedure here
    @LName varchar(1000)
    @City varchar(1000),
    @State varchar(1000),
    @License varchar(1000)

AS

declare @Count as int
declare @x as int
declare @wLName as varchar(2000)
declare @wCityas varchar(2000)
declare @wStateas varchar(2000)
declare @wLicense as varchar(2000)

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    if right(rtrim(@LName),1) <> ';'
    begin
        set @LName = @LName + ';'
    end

    set @Count = PATINDEX('%;%',@LName)

    set @wLName = '(tblUsers.LName = '''
    while @Count <> 0
    begin
        set @wLName = @wLName + left(@LName, @Count - 1)

        set @LName = stuff(@LName, 1, @Count, '')
        set @Count = PATINDEX('%;%',@LName);

        if @Count <> 0
        begin
            set @wLName = @wLName + ''') OR (tblUsers.LName = '''
        end
        else
        begin
            set @wLName = @wLName + ''')'
        end
    end

    print cast(@wLName as varchar(5000))

    -- Insert statements for procedure here
    SELECT
        tblUsers.FullName,
        tblUsers.Addy1,
        tblUsers.Addy2,
        tblUsers.City,
        tblState.StateAbbr,
        tblUsers.Zip,
        tblUsers.Zip4
    FROM
        tblUsers
        INNER JOIN tblState ON tblUsers.FK_StateID = tblState.StateID
        INNER JOIN tblUserDetails ON tblUsers.UserID = tblUserDetails.FK_UserID
    WHERE
        (@wLName) OR
        (tblUsers.City = @City) OR
        (tblState.StateAbbr = @State) OR
        (tblUserDetails.CDLType = @License)
END
So when I print do an exec getUsersAddress 'Johnson;Smith', 'City;Test City', 'TX;OK', 'Class A;Class B'

@wLName comes out as (tblUsers.LName = 'Johnson') OR (tblUsers.LName = 'Smith')

However, I can't save the procedure as it gives me an error of:
An expression of non-boolean type specified in a context where a condition is expected, near 'OR'.

When I copy and paste the @wLName value in place of @wLName in the Where clause it works, so how can I get the @wLName variable to work in that Where clause?

View Replies !

IF ELSE Statement In WHERE Clause
actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it.

i have try to use:

if @IssueUserID = ' ' Select....(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services.... :confused: is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help.....

================================================== ==
SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount,
tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation
FROM tbl_Receipt WITH (nolock) INNER JOIN
tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN
tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN
tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID

WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @DateFrom, 103) AND CONVERT(datetime, @DateTo, 103)) AND
(tbl_Receipt.IssueUserID = @IssueUserID)
ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeID

View Replies !

Right Use Of Like In An Or Clause Of Where Statement
I have the below where clause on a select

where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and ((pa2.df_institutionname not like ('ACT%')
or pa2.df_institutionname not like ('county%')))

It returns records that have either one of the not like statements. If I comment out the line and use below it will work for one of the statements

where substring(pa.other_id_number,5,1) = @monthid
and pa2.df_patstatus like 'act%'
and pa2.df_institutionname not like ('ACT%')

How can I get the two to work together. I thought the parenthesis would work but no luck.

View Replies !