Beginner Question: What Is The SELECT Here For? - In Stored Procedure
Apr 23, 2008
Hi:
I am learning "stored procedure": whis is the SELECT 1, 2, and 3 for in below
======================ALTER PROCEDURE [dbo].[AddToFavourites]
(
@Username varchar(20),
@Favourite varchar(20),
@MaxFavouriteUsers int
)
AS
IF ((SELECT COUNT(*) FROM Favourites WHERE @Username = u_username AND @Favourite = f_username) <> 0)
SELECT 2
ELSE IF ( (SELECT COUNT(*) FROM Favourites WHERE @Username = u_username) >= @MaxFavouriteUsers)
SELECT 3
ELSE BEGIN
INSERT INTO Favourites
(u_username, f_username)
VALUES
(@Username,@Favourite)
Hello,I have 2 tables: Employees(EmployeeID, FirstName, LastName),EmployeeApplication(CreatedByID, EmployeeID,Date1, Date2, Content...)I need to create stored procedure that will return employee First andLast names (plus some other columns) instead *both* ID's inEmployeeApplication.I know how to do it with only one - I am using simple join, but withtwo I have no idea...Can anyone help me ?Richard...
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I am having problems using IF statements. In fact, I don't even know if it is possible to use "IF" in a SELECT statement. Here's my dilemma:
For each employee in my database, I have a numeric rating. I want to assign the rating to a category (green, yellow, red), based on the range the rating falls into.
My data currently looks like this: EmpName Rating employeeX 100 EmployeeY 85 EmployeeZ 25
After the magic of SQL, I want the data to end up looking like this (actually, my boss wants it like this):
EmpName Green Yellow Red employeeX 100 EmployeeY 85 EmployeeZ 25
The numeric rating would be inserted into the appropriate column, based on these ranges: 95 to 100 = green 80 to 94 = yellow < 80 = red
I thought I could use a CASE statement, but I just learned that I can't do comparisons (I'm obviously a beginner). And I can't figure out how to use an If statement (or IIF).
I'm new to SQL, so I apologize for the easier question compared to some of the stuff I was reading on here. I work for a construction company and the accounting software they use, uses SQL in the report writer it looks like. So I've been trying to learn the basics on the fly for a while now. Anways, on to the question... Basically there are two different tables of data I'm trying to pull into one report. I'll give my code for it and then describe the outputs. ********************************** [SELECT bdglin.hrsbdg FROM bdglin WHERE bdglin.recnum = {jobcst.jobnum}] + [SELECT sbcgln.chghrs FROM sbcgln, sbcgln INNER JOIN prmchg ON sbcgln.recnum = prmchg.recnum WHERE prmchg.jobnum = {jobcst.jobnum} and prmchg.status = 1] ********************************** I'm starting to think it's something to do with the brackets in there. If I leave the code like that, it's printing the bdglin.hrsbdg out if there is a numeric value (which is good), but it is multiplying it by some number that I can't figure out where it is coming from. So if I'm expecting to see "5" it is either outputting "5" or some whole number multiple of it. If I add these { } brackets around the whole block of code. The outputs are correct but display like: " {5 + 0} " So 5, the correct budget number and 0 if there is nothing in the change order screen. Basically the multiples are gone, but it's not adding together the budget and change order numbers. I spent a fair amount of time tinkering with it yesterday and today a bit with a clear head, and read as much as I could online about SQL to see I needed to ask someone knowledgable if I was going to make headway. Thank you very much in advance for your time and any help you can lend.
This stored procedure is missing one thing. The product information, the only way i can link those fields, is if i grab the Item_no, from the Tbl_product_Activity table. The only problem with this is i keep gettin too many records when i try an inner join. Is there someone way i can List it in the selct statement for the sub guery. Tbl_Product_Activity is the only table in the stored procedure that has the Item_no. But as you can see in the subquery im grabbing fields from there, but i exculde the item number, so i can group and get a quantity count. How can i get that field in the stored procedure , so im able to link other fields from the product table. would i be able to add another subquery that looks like this: SELECT
ITEM_NO FROM TBL_PRODUCT_ACTIVITY ???? any help will be greatly appreciated.
Code Block set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[PROC_RPT_BANFIELD_PRODUCT_ACTIVITY] (@Region_Key int) AS BEGIN
SELECT DAS.Territory_Code, DAS.SR_Code, DAS.Customer_code, Product_Activity.Item_status_code, DATEPART(Dw, DAS.Daily_activity_statistics_datetime) AS DayOfWeek, DAS.Daily_activity_statistics_datetime, COUNT(*) AS Visit_Count, Product_Activity.Product_Qty, ud_customer_hierarchy.type_code, ud_customer_hierarchy.group_code, Qry_Sales_Group.Region_Key, Qry_Sales_Group.Region, Qry_Sales_Group.Name AS Territory_Name, customer.name AS customer_name FROM dbo.Tbl_Daily_Activity_Statistics AS DAS INNER JOIN ud_customer_hierarchy ON DAS.Customer_code = dbo.ud_customer_hierarchy.customer_code INNER JOIN Qry_Sales_Group ON DAS.SR_Code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS INNER JOIN customer ON DAS.Customer_code = dbo.customer.customer_code LEFT OUTER JOIN (SELECT Customer_code, Visit_date, Item_status_code, SUM(Item_Qty) AS Product_Qty FROM Tbl_Product_Activity WHERE Returns_reason_code='1' or returns_reason_code = 'null' GROUP BY Customer_code, Visit_date, Item_status_code) AS Product_Activity ON DAS.Customer_code = Product_Activity.Customer_code AND DAS.Daily_activity_statistics_datetime = Product_Activity.Visit_date WHERE (DAS.question_code = '9999') GROUP BY DAS.Territory_Code, DAS.SR_Code, DAS.Customer_code, Product_Activity.Item_status_code, DAS.Daily_activity_statistics_datetime, Product_Activity.Product_Qty, dbo.ud_customer_hierarchy.type_code, dbo.ud_customer_hierarchy.group_code, dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Sales_Group.Region, dbo.Qry_Sales_Group.Name, customer.name HAVING (dbo.ud_customer_hierarchy.type_code = 'BA') AND (dbo.ud_customer_hierarchy.group_code = 'YES') AND (DAS.Daily_activity_statistics_datetime >= DATEADD(dd, - 7, DATEADD(dd, 1 - DATEPART(w, GETDATE()), CONVERT(varchar, GETDATE(), 101)))) AND (DAS.Daily_activity_statistics_datetime< DATEADD(dd, 0, DATEADD(dd, 1 - DATEPART(w, GETDATE()), CONVERT(varchar, GETDATE(), 101)))) AND @REGION_KEY=REGION_KEY ORDER BY DAS.Customer_code, DAS.Daily_activity_statistics_datetime, Product_Activity.Item_status_code END
Hi, I want to set the max and min of a query to the variable @Value1 and @Value2. However, when I do the following, both @Value1 and @Value2 pointed to the latest data. Do I have to use a subquery to do that or is that possible to do it directly? DECLARE @Value1 DECIMAL(7,2) DECLARE @Value2 DECIMAL(7,2) SELECT TOP 20 @Value1=MIN(Value1), @Value2=MAX(Value2)FROM OrdersGROUP BY OrderID, Date
I have the stored procedure snipet below and it does not return anything. What am I doing wrong here? I works ok if I need all the records for the dataset and don't use the WHERE and LIKE. CREATE PROCEDURE dbo.search @Field VARCHAR (20), @KeyWord VARCHAR (200), @errCode INT OUTPUT ASBEGIN DECLARE @guid uniqueidentifier DECLARE @subject NVARCHAR SELECT @guid = guid, @subject = subjectFROM myTable WHERE @Field LIKE @KeyWord SELECT guid, subjectFROM myTable WHERE @Field LIKE @KeyWord
In Informix you can use a stored procedure as part of a SELECT statement:
SELECT ord_num, ord_date, ship_date, business_day_diff(ord_date, ship_date) FROM order_table
business_day_diff is a stored procedure that uses a custom calendar to compute business days. In fact, Informix stored procedures can return any data type. Apparently SQL Server stored procedures cannot be used in a SELECT like this.
Does anyone know of a straightforward way to accomplish the same purpose?
How do you use the value of one select statement within another select statement inside the same stored proc?
Here is my scenario: I need a row id from one select statement to make another select statement. I figured instead of making two calls from within my code I could just call one stored procedure which would have to be faster!
I have written plenty of select statement sp's but this is the first time I ever needed to do something like this.
How could I use SELECT TOP in stored procedure For ex I want convert this line to Stored procedure
"Select Top "&intArticles&" * From tblArticles WHERE published = 1 ORDER BY Adate DESC, Atime DESC;"
I dont want use ROWCOUNT because its not working good it will not show some data of some fields for example if I have Subject and Body fields, in results it does not show body content, and it will show just subject :confused:
this is my current code
CREATE PROCEDURE [dbo].[SP_Articles] ( @Articles int ) AS SET ROWCOUNT @Articles SELECT tblArticles.* FROM tblArticles WHERE published = 1 ORDER BY Adate DESC; SET ROWCOUNT 0 GO
Hey all! I have a quick question. Is it possible do formulate a select query where one of the tables is a recordset returned from a stored procedure? If so, what's the syntax?
In other words, if I have a stored proc whose last command is:
selectt.[Region_Code], t.[Country_Code], sum([Total]) as [Total] from#tmp t group by[Region_Code], [Country_Code] order by[Region_Code], [Country_Code]
Then can I somehow get a handle on that cursor and join it to another table etc? Thanks!
I have a stored procedure that has many select statements in it. I want to call and execute the procedure in my c# code and put data returned by each select statement into separate grids. I could just write the select into the c# code and then execute as a dataset making that dataset the datasource for whatever grid but that defeats the purpose of stored procedures. Can I capture the data returned by each select in the stored procedure and have it put into a grid when the c# code is ran.
HelloI have many different tables, that most have the same structure.. they allhave some primary key, some fields and mylinkid ... so what I want to makeis to create a stored procedure, where I can pass the table-name, primarykey, and the new mylinkid, what should happen is, that the procedure copiesthe entrie, where i passed the id, to a new row and the mylink-id should getthe new value...example:mytable 1id=1 (primary key autoincrement)mylinkid=233field1=asdffield2=blablai call my procedure like: duplicate_entry 'mytable',1,4the result should then beid=2 (primary key/autincrement)mylinkid=4field1=asdffield2=blablaI was thinking to make a Stored Procedure, that copies the entier row with aselect * into mytable (SELECT * from mytable where id=1) and afterwards iupdate the mylinkid ..... but there i have problem with the primary key.The other solution that will work, but I won't like is to get with thesp_fields all fields from that specific table, build the select into withthe correct fields without the id-field... that should work, but was askingmyself if there is somethign better...Has someone a good idea for that?Thanks
I have code simililar to the following in a stored procedure:
CREATE PROCEDURE GetGroupMembers ( @GroupID INT )
AS SELECT TMembers.MemberID, VCurrentMemberGroups.GroupID, THonorsMembers.HonorID FROM THonorsMembers LEFT OUTER JOIN TMembers ON THonorsMembers.MemberID= TMembers.MemberID LEFT OUTER JOIN VCurrentMemberGroups ON TMembers.MemberID= VCurrentMemberGroups.MemberID WHERE (VCurrentMemberGroups.GroupID = @intGroupID) AND TMembers.DeleteDate IS NULL ORDER BY TMembers.MemberID
RETURN GO
When I call this stored procedure in Query Analyzer, it takes two minutes to execute. The web pages that rely on it time out before that. If I copy just the select statement and substitute the group I'm working with for @GroupID, it takes less than a second for the statement to run and display results.
Can anyone tell me why a select statement called through a stored procedure would take minutes longer than one ran from Query Analyzer? I have many other stored procedures that are just select statements like this with a variable or three and they have no problems completing execution in a timely manner.
I have a complex stored procedure that returns one or more nvarchar values given a syntax similar to this:
sp_MyProcedure 'Param1', 'Param2'
My issue is that the end goal is to get these values into a SQL query that I can call directly from something like Excel (no VBA or anything can be used). So far, I have come up with this:
INSERT INTO [#tmpTable] EXEC sp_MyProcedure 'Param1', 'Param2'
SELECT * FROM AnotherTable
WHERE (AnotherTable.ID IN (select * from [#tmpTable])) AND (AnotherTable.Year>2007)
drop table [#tmpTable] ********************************************************************************************************
That works, but it is incredibly sloppy once I have need to make 4 or 5 temp tables like that and then select from all of them inside the same query. So does anyone have any suggestions? Can I use a function to help out?
So I have simply procedure: Hmmm ... tak sobie myślę i ...
Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w ten sposob co napisalem kilka postow wyzej (select ID as UID from aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic @user_id int, @topic_katId int, @topic_title nvarchar(256), @post_content nvarchar(max)as insert into [forum_topics] (topic_title, topic_userId,topic_katId) values (@topic_title, @user_id, @topic_katId) insert into [forum_posts] (topic_id, user_id,post_content) values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id". Can anyone help me ? I tried lots of ways but nothing works.
i have this stored procedure: ALTER PROCEDURE dbo.SearchContact@searchCriteria nvarchar(128)AS Select FstNam1,FstNam02 from Contacts where FstNam1 like '%'+@searchCriteria+'%' RETURN In my .aspx i have a SqlDataSource named SqlDataSource1 which have asocciated this stored procedure to select operation and parameter source of 'searchCriteria' is Control and ControlID is "TextBox1". Also i have a gridview with source this sqlDataSource1 and a button . When i click this button i want to take value entered in textbox and send it to above stored procedure and show returned infos in my gridview. i put in Button1_Click(object sender, EventArgs e){ SqlDataSource1.SelectParameters["searchCriteria"].DefaultValue = Session["searchContact"].ToString();xxxxxx } what i need to have to xxxxx to work fine ? I searched for a solution and i find a lot of posibilities, but all are fragmented.........pls give a solution ...thx for help. P.S: Sorry for my english.
This would been pretty easy in C#, but I need some help to solve this in sql ( the report takes an sql function) Does anyone know if this is possible in sql?
Hi, nice to meet you all. I'm new developer and need help. Normally i use "sqldatasource1.select()", but need to fill argument, what is argument? Actually the main point i wan to return a value from stored procedure or got any methods can do this way? Hope you all can help me.... Thank
hi! i have two tables: Pictures (PictureID, UserName, UserID, DateAdded, Comments int) and UserVisits (VisitID, UserName, UserID, PictureID, NewComment bit) if a new comment is added on a picture with PictureID = 4, i update NewComment from UserVisits with 1 (true) where PictureID = 4 to all rows, and if a user visits a picture with PictureID = 5, NewComment will be set to 0 back where pictureID = 5 (something like asp.net, when a reply is added to a therad) i need to make a stored procedure with UserName input parameter witch selects ALL pictures from Pictures table and selects NewComment (true or false) from UserVisits witch corresponds with that UserName !!! Is possible that in UserVisits to not have yet any column where UserName corresponds with the user who visits if the user did not visited that picture yet (this stored procedure will be used on a Repeater witch lists all the pictures) if i was not clear please tell me, sorry i am verry bad at T-SQL language thanks in advance
Hi everybody, I'm just writing a stored procedure in my MS SQL 2005 server. I wanted to ask if there is any possibility to execute a select statement to a MySql database on another server? Does anybody have an idea? Best regards, ALEX
I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.
How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.
Any tips highly appreciated! If I'm not being clear I'll gladly post more details.
Having a little trouble getting this one to work. I have a stored procedure that selects items from the table... SELECT fldDate, shortTitle, longTitle, Email, POC, News, guidFROM tblNews I need to change it so it selects only the year needed. For instance, only items from 2004. SELECT fldDate, shortTitle, longTitle, Email, POC, News, guidFROM tblNews WHERE fldDate = @myParameter -Say myParameter = 2004 or whatever year is needed The table field fldDate is a datatime field that has date and time stored and I can put any parameter into the stored procedure. How can I be sure to only get a certain year? Thanks, Zath
Does anybody know what is wrong with this code from a stored procedure: DECLARE tables_cursor CURSOR FOR SELECT tf_change_out_id, destination, re_table, date_in FROM tf_change_out_table WHERE date_out = NULL ORDER BY tf_change_out_id
Here is the error I'm getting: Error 107: The column prefix 'tf_change_out_table' does not match with a table name or alias name used in the query.
