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 Complete Forum Thread with Replies
Related Forum Messages:
Using User Input As Part Of A SELECT Statement
I know this is an easy one and I know I've read it somewhere, but I can't seem to write the correct format to run correctly. I am trying to build a SELECT statement base on the selected values of a dropdown list on a webform. The selected values will be part of the Table name.. ("client_info" & location_option.selecteditem.value) Can someone show me the correct syntax for adding a form variable into a SELECT statement? Thanks
View Replies !
Excluding Part Of Select Statement If No Data Is Returned In Results
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
View Replies !
How Do I Use A @variable To Hold On Value Return From An Exec ('string Command') Statement.
How do I use a @variable to hold on value return from an exec ('string command') statement. Example for: declare @OldID int declare @cmd varchar(255) declare @db varchar(25) declare @OldOwner varchar(25) set @db = 'DBNAME' set @OldOwner = 'USERNAME' select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" exec (@cmd) How can I use @OldID to hold on UID return from this statement. When I try use: select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" then I'm getting a error message: @OldID not declare. Thanks.
View Replies !
Need To Return X Rows But Select Top Doesn't Work With Variable
I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of. Thanks!
View Replies !
Return SP In A SELECT Statement Column
I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement. I want the following output: CatID | CatTitle | CatTree 001 | News | exec sp_DisplayTree(@CatID) My code I tried doesn't work: SELECT C.CatID As CatID, C.CatTitle As CatTitle, CatTree = (exec sp_DisplayTree C.CatID) FROM Cats As C WITH (nolock) I cannot find a solution to my solution, please help...
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 !
Return Select Statement Or Values Using SqlDataSource?
Hello all, I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be? I have a SqlDataSource" <asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)" UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure"> <SelectParameters> <asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="Action" Type="Byte" DefaultValue="2" /> </UpdateParameters> </asp:SqlDataSource> And my SP:/* 09-19-07 Used to update affiliate profile */ CREATE PROCEDURE spUpsertAffiliateProfile @Action tinyint, @AffiliateId int, @MemberId int = -1, @CategoryId int, @WebAddress varchar(50), @Email varchar(50), @Comments varchar(1500) AS SET NOCOUNT ON -- Find errors first, check is not needed if deleting IF @Action <> 3 IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%') BEGIN SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId) BEGIN SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 AND @AffiliateId = 0-- insert BEGIN INSERT INTO tAffiliateMaster (member_id, category_id, profile_web_address, profile_email_1, comments) VALUES (@MemberId, @CategoryId, @WebAddress, @Email, @Comments) SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id RETURN END ELSE IF @Action = 2 AND @AffiliateId > 0-- update BEGIN UPDATE tAffiliateMaster SET category_id= @CategoryId, profile_web_address=@WebAddress, profile_email_1=@Email, comments=@Comments WHERE affiliate_id = @AffiliateId AND member_id = @MemberId SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id RETURN END ELSE IF @Action = 3 AND @AffiliateId > 0-- delete BEGIN DELETE tAffiliateMaster WHERE affiliate_id = @AffiliateId SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id RETURN END GO My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event? Thanks!
View Replies !
Overloading Select Statement Return Values
Hey guys, This is what I think and hope is a fairly straight forward SQL question Essentially, I have a table which has the following columns that are relevent to my question: PROJID ACTIVITY_NAME COMPLETION_DATE Rows in this table are, for example: PROJID ACTIVITY_NAME COMPLETION_DATE 1 Prepro 10/12/2007 3:42:30 2 Prepro 10/13/2007 9:16:27 2 QA 10/13/2007 10:00:01 2 Delivery 10/14/2007 09:31:12 etc. So, really the key is the PROJID & the ACTIVITY_NAME (really, there's a unique column ID, but for this question, I'll leave it at that). (Though this should be much easier to accomplish in code, the system is not built that way so) Is there a good way that I could return a status for a given PROJID based on whether a row exists for a given PROJID). In other words, ultimately, I would like to return something like this: PROJID LAST_ACTIVITY ---------------------------------------------- 1 Prepro 2 Delivery where the activity order (in this case) is Prepro, QA, Delivery. So because a Delivery row exists for PROJID 2, then the LAST_ACTIVITY would return "Delivery" and because only Prepro exists for PROJID 1, the LAST_ACTIVITY returned would be Prepro I really appreciate the help Thanks, Steve
View Replies !
Return NULL Values In SELECT Statement With INNER JOIN ?
If I try to run the code below, and even one of the values in the INNER JOIN statements is NULL, the DataReader ends up with zero rows. What I need is to see the results even if one or more of INNER JOIN statements has a NULL value. For example, if I want info on asset# 2104, and there's no value in the DriverID field, I need the rest of the data to display and just have the lblDriverName by blank. Is that possible? <code> Sub BindSearchGrid() Dim searchUnitID As String Dim searchQuery As String searchUnitID = tbSearchUnitID.Text lblIDNum.Text = searchUnitID searchQuery = "SELECT * FROM Assets " & _ "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _ "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _ "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _ "INNER JOIN AssetCategories ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _ "INNER JOIN Store ON Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '" & searchUnitID & "'" Dim myReader As SqlDataReader myReader = Data.queryDB(searchQuery) While myReader.Read If Not IsDBNull(myReader("Store Name")) Then lblStrID.Text = myReader("Store Name") If Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text = myReader("AssetCategory") If Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text = myReader("Condition Description") If Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text = myReader("DepartmentName") If Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit ID") If Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year") If Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make") If Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model") If Not IsDBNull(myReader("Mileage")) Then lblMile.Text = myReader("Mileage") If Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text = myReader("Vin Number") If Not IsDBNull(myReader("License Number")) Then lblLicNum.Text = myReader("License Number") If Not IsDBNull(myReader("Name")) Then lblDriverName.Text = myReader("Name") If Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text = myReader("DateAcquired") If Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text = myReader("DateSold") If Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text = myReader("PurchasePrice") If Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text = myReader("NextSchedMaint") If Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR") If Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW") If Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text = myReader("Crane Capacity") If Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text = myReader("Crane Certification") If Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text = myReader("Repair Cost") If Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text = myReader("Estimate Replacement") If Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text = myReader("SalvageValue") If Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text = myReader("CurrentValue") If Not IsDBNull(myReader("Comments")) Then lblCom.Text = myReader("Comments") If Not IsDBNull(myReader("Description")) Then lblDesc.Text = myReader("Description") End While End Sub</code>
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 !
Select Statement To Return Most Current Syscreated Date On Duplicates
I have a db that has duplicate customer records. If I run the following select statment against the appropriate table it returns the duplilcate records in the result set. However, from this list I want to add an additional select statement embedded into the query that will actually return only those records with the most current syscreated date. Example of script I'm using--- select cmp_fadd1, syscreated, cmp_name, cmp_code from cicmpy where cmp_fadd1 in (select cmp_fadd1 from cicmpy group by cmp_fadd1 having count(1) = 2) order by cmp_fadd1,syscreated desc The results is: Address Syscreated date Customer 1622 ONTARIO AVENUE 2005-06-15 22:19:45.000 RELIABLE PARTSLTD 1622 ONTARIO AVENUE 2004-01-22 18:10:05.000 RELIABLE PARTS LTD PEI CENTER 2006-01-05 22:03:50.000 P.G. ENERGY PEI CENTER 2004-01-22 17:57:56.000 P.G. ENERGY From this I want to be able to select ONLY those records with the most current syscreated date or those records with 2005-06-15 and 2006-01-05 Can anyone help me with creating this? Thanks Cyndie
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 !
Use A Variable Along With The FROM Clause In SELECT Statement
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts. What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*). This is the code in my procedure.. DECLARE @tab_list CURSOR set @tab_list = CURSOR FOR select * from table_list OPEN @tab_list DECLARE @tab_name varchar(256) DECLARE @rec_cnt int FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt select count(*) from @tab_name This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table. Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there. Please help me to construct the select statement that is similiar to x=<table name> select * from x where x is a variable and the table name gets substituted. what is the syntax for it ?
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 !
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 !
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 !
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 !
Return Part Of A String
how can you return part of a string and convert it into an integer value? Maybe like this: convert(left(column, 3) as int) does that work? The Yak Village Idiot
View Replies !
SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes. Here are the task steps. [Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step. [Execute SQL Task] - Log an entry to a table indicating that the import has started. [Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works. [Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile. If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS. If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post. Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction. CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate] /* The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate. If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset. Otherwise it returns a FALSE value in the IsNewFile column. Example: exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0 */ @ProcessName varchar(50) , @FileCreateDate datetime , @IsNewFile bit OUTPUT AS SET NOCOUNT ON --DECLARE @IsNewFile bit DECLARE @CreateDateInTable datetime SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName) BEGIN -- The process exists in tbl_ImportFileCreateDate. Compare the create dates. IF (@FileCreateDate > @CreateDateInTable) BEGIN -- This is a newer file date. Update the table and set @IsNewFile to TRUE. UPDATE tbl_ImportFileCreateDate SET FileCreateDate = @FileCreateDate WHERE ProcessName = @ProcessName SET @IsNewFile = 1 END ELSE BEGIN -- The file date is the same or older. SET @IsNewFile = 0 END END ELSE BEGIN -- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE. INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate) VALUES (@ProcessName, @FileCreateDate) SET @IsNewFile = 1 END SELECT @IsNewFile The relevant Global Variables in the package are defined as follows: Name : Scope : Date Type : Value FileCreateDate : (Package Name) : DateType : 1/1/2000 IsNewFile : (Package Name) : Boolean : False Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings. General Name = Compare Last File Create Date Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate. TimeOut = 0 CodePage = 1252 ResultSet = None ConnectionType = OLE DB Connection = MyServerDataBase SQLSourceType = Direct input IsQueryStoredProcedure = False BypassPrepare = True I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate. SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output Parameter Mapping Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1 Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1 Result Set is empty. Expressions is empty. When I run this in debug mode with this SQL statement ... exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the following error message appears. SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning. Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "MyPackage.dtsx" finished: Failure. When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure. When I run this in debug mode with this SQL statement ... exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message. SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning. Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "MyPackage.dtsx" finished: Failure. The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives? The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing. Thanks for your help.
View Replies !
'Return' Statement In A Function,Get,or Operator Must Return A Value....Question
I'm a Reporting Services New-Be. I'm trying to create a report that's based on a SQL-2005 Stored Procedure. I added the Report Designer, a Report dataset ( based on a shared datasource). When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure. I'll only reproduce one instance of the error for the sake of brevity. [rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value. I've searched on this error and it looks like it's a Visual Basic error : http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx My guess is that BIDS is creating some VB code behind the scenes for the report. I can't find any other information that seems to fit this error. The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report. BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables. Can anyone help ? Thanks,
View Replies !
How Do I Return Results Using An Entire Column As Part Of The Search Parameter
Hi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long. eg. CREATE TABLE [dbo].[tPopupKeywords]( [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] INSERT INTO dbo.tPopupKeywords(title, description) SELECT 'check', 'desc' UNION ALL SELECT 'for', 'desc' UNION ALL SELECT 'keywords', 'desc' select dbo.tpopupkeywords.title where 'This is an example of a passed in string to check if any keywords are returned.' LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select?? expected results.....: check keywords
View Replies !
Storing Part Of An Express To A Variable
I know this can be done, because I saw it and tried it once. But did not use it at the time and consequently forgot how. I am storing an expression to a variable @Var Varchar(50) @Var = ('aa','bb','cc') I want to: Select * from Table WHERE Field IN @Var Substituting my variable for the " ('aa',bb','cc') which I can build dynamically...thus the use. Purpose: I have an aggregate query that currently has a CASE statement based on the contents of ('aa',bb','cc'). However, those contents can change. The aggregate will not allow the subquery (SELECT Field FROM Table). What I remember looked something like: SELECT * FROM Table WHERE field in ' & @Var &'.
View Replies !
Making Part Of A Table Name Into A Variable
I have a huge question, it's for my job im doing now. I have a table with the name TWO.dbo.SVC06105. I want to be able to take the word "TWO" and put that into a variable. I know this doesn't look correct, but I want to be able to do something like this: DECLARE @GPNAME CHAR(100) SET @GPNAME = 'TWO' SELECT * FROM @GPNAME.dbo.SVC06105 I know the SELECT statement isn't correct, but I hope that you guys understand what i'm trying to do through this example. BTW, I am new to this team so nice to meet you all. L8er
View Replies !
Passing A Variable To The @query Part Of Xp_sendmail
i have the following code in a stored procedure, i want to pass a @LoginID variable as in the WHERE clause belowbut i get the error message:- ODBC error 137 (42000) Must declare the variable '@LoginID'. Is there anyway to pass a variable to the @query part of xp_sendmail ?? EXECUTE master..xp_sendmail @recipients = 'pauln@tempestconsultants.com', @message = 'Error inserting record in tbl_Login_Audit_Trail when setting fb_blocked =1. Client effected:', @query = 'SELECT fs_LoginName, fs_plcName FROM tbl_Login L, tbl_Company C WHERE L.fa_LoginID = @LoginID', @subject = 'Error on INSERT into tbl_Login_Audit_Trail'
View Replies !
Problem Running SP_EXECUTESQL With OpenRowSet (with Variable) As Part Of The Query
Hi, I had problem when combining OpenRowSet and SP_EXECUTESQL, when i tried to run the following query, it complaints that RESID is not declared. any idea how should i put the query so i will pass @RESID as 1 of the parameter? BTW, i know that the SP_EXECUTESQL is able to run query which length up to 8000, but how about the parameter? DECLARE @SqlToRun NVARCHAR(MAX) DECLARE @RESOURCEIDS NVARCHAR(MAX) SET @RESOURCEIDS = REPLICATE(CAST('ABC' AS NVARCHAR(MAX)), 1000) SET @SqlToRun = N'SELECT * FROM OPENROWSET(''SQLNCLI'', ''server=;database=;uid=;pwd='', ''SELECT * FROM WHERE COL=@RESID'')' DECLARE @PARAMS NVARCHAR(MAX) SET @PARAMS = ''@RESID NVARCHAR(MAX)' EXECUTE SP_EXECUTESQL @SqlToRun, @PARAMS, @RESID = @RESOURCEIDS Regards, Derek
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 !
Excel XP Removes AS Part Of Statement
I am trying to help an individual with a MS Query. In Excel 97 MS Query the script was written: TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS YEAR In Excel XP I can run an existing Query and it returns the data as expected, but when I view the query it is: TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR' However if I try to create a new query using XP MS Query then the AS YEAR or AS 'YEAR' will be removed by MS Query and my column header will now be: TO_CHAR(LABTRANS.STARTDATE, 'YYYY') Here are my examples: MS Excel 97 Query SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC, TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR' FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK')) AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE MS Excel XP Query SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC, TO_CHAR(LABTRANS.STARTDATE, 'YYYY') FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK')) AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE In Excel XP, no matter what I put in the AS part of the statement when I execute the script it runs and removes the AS part from the statement. This only happens when I run the script from MS Query, if I stay in Excel and just refresh the data, then it works fine. I did not have this problem in Excel 97. Any suggestions?
View Replies !
SQL Statement - Part Dates Selection
Hi all, I have a database with a field which contains a load of dates in this format 01/03/1998 What I want to do is select all the records from the database where the month of the date is what ever, this month or the month the customer selected etc. I suspected it may be something like the following (which doesnt work) sql = "SELECT * FROM avail_lowermill WHERE CH_Arrival.month = " & showmonth & " ORDER by CH_Arrival" Can anyone suggest how I can do this? Thanks in advance
View Replies !
SubQuery In Both SELECT And WHERE Part. How To Optimise?
Ok I have the following SQL, I have a subquery in the SELECT part but also the same subquery in the WHERE part as well.What I'm trying to do is get all parents that have children OR get all parents with no children OR just get all parents regardless (@HasResponses is a BIT that can be 1, 0 or null). At the same time I want to count the total number of children in the select list, but I'm having to copy the same subquery in the SELECT and WHERE parts which doesn't seem terribly optimal to me (maybe it is, that's why I'm asking). I've tried referencing the column alias in the select list (AS [Responses]) for the where part (@HasResponses = 0 AND [Responses] = 0), but it doesn't seem to work.Is this the most optimal way to do it? Is there a better way? I'm working with SQL Server 2005.SELECTf.FeedbackText AS [Feedback Comment],u.Name AS [Feedback Author],f.CreatedDate AS [Created On],(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) AS [Responses]FROM Feedback fINNER JOIN [User] u ON f.StaffID = u.StaffIDWHERE f.CreatedDate >= @DateFromAND f.CreatedDate <= @DateToAND(@HasResponses IS NULLOR(@HasResponses = 1 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) > 0)OR(@HasResponses = 0 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) = 0))
View Replies !
Accessing The Return Variable Of An Insert
When I create a tableadapter to do an insert it added SELECT EventDate, EventID, org_id, ROMEventID FROM ROMEvents WHERE (ROMEventID = SCOPE_IDENTITY()) at the end. This looks useful to me since it implies that I can access ROMEventID which is the auto-incremented identity field. How would I reference the variable in my .cs file? My insert is working as ROMEventsTableAdapter ROMEventInsert = new ROMEventsTableAdapter(); ROMEventInsert.InsertIntoROMEvents(theDate, EventID, org_id); Thanks for any help
View Replies !
How To Return No Rows If A Variable Is NULL
Hello, I have a stored procedure that accepts a number of different input parameters that populate some variables in my stored procedure. I want to have this stored procedure return nothing if some of these variables aren't filled out (they are populated by a search page the user fills out). I'm not very familiar with writing stored procedures, so any help you can give me is appreciated. Thanks!
View Replies !
Stored Procedure Return Value And A Variable
hi everybody, I have two stored procedures lets say A and B and a variable inside stored procedure A lets say @BReturn, now, what is the syntax to execute B inside A and to store its returned value in @BReturn? any help is greatly appriciated. devmetz.
View Replies !
|
TRACKER
