If Statement In Select Of Stored Procedure
Dec 19, 2000Hi,
Can you use an IF statement in a Select statement within a stored procedure?
If so, how?
Mark
ADVERTISEMENT
T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
Need To Run Stored Procedure In Select Statement
Aug 21, 2000I am trying to execute a stored procedure in a select statement.
I have a stored procedure that returns the next number in a sequence (much like an identity column)
I am trying to do an insert statment like the following
insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar
Is there any way I can do this?
I do not want to use a trigger or an identity column.
Stored Procedure In A Select Statement
Apr 23, 2007Hi All,
Can i run a stored procedure with in a SELECT statement
like below?
Select * from EmployeeDetails where EmployeeName in (.. Some Stored procedure to return me some set of Employee Names)
Is there a better way of doing it?
Thanks in advance
vishu
Bangalore
Stored Procedure - Using Value From A Select Statement
Jul 30, 2007Hello,
I have written a stored procedure which has in it a select statement. This returns a single record (I used the "top 1" line to ensure I only get a single record). I want to use a value returned by this statement (not the primary key) further on in the stored procedure - so assigning it to a variable would be handy.
Is this possible? If so, how do I do it?
Thanks.
Trying To Use Stored Procedure In A Select Statement
Jul 7, 2006Hi there, I am trying to return data from a select statement that is running a stored procedure.
I am running on SQL 2000 SP4. I have created a 'loopback' linked server as described in a technet article. (It's pointing at itself) I can then use the following select statement to get data back in the select statement from a stored procedure.
select * from openquery(loopback,'exec DWStaging.dbo.PokerStaging')
I am trying to get data back from my own Stored Procedure, but continue to get the following error:
Server: Msg 7357, Level 16, State 1, Line 1
Could not process object 'exec DWStaging.dbo.PokerStaging'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process the object:ProviderName='SQLOLEDB', Query=exec DWStaging.dbo.PokerStaging'].
If I try the same syntax with the sp_who stored procedure, it works and I get data back fine. But, If I try it with the sp_who2 stored procedure I get the same error as above.
select * from openquery(loopback,'exec sp_who') --> Works fine
select * from openquery(loopback,'exec sp_who2') --> Doesn't work
Does anyone know what the difference is between the Stored Procedures that work with this syntax and those that don't? Is there a way I can change my stored procedure such that it would cause it to work?
PS: The following code was used to create the linked server, and then a security pass though as a certain account was addedDECLARE @provstr varchar (2000)
SET @provstr = 'PROVIDER=SQLOLEDB;SERVER=' + @@SERVERNAME
EXEC sp_addlinkedserver 'loopback', @srvproduct = 'MSSQL',
@provider = 'SQLOLEDB', @provstr = @provstr
Stored Procedure In SELECT Statement ?
Nov 8, 2006Hello,
I want to recover rows returned by a stocked procedure into a select statement, something like this:
select * from (exec sp_helpuser) us where UserName like 'dbo'
This syntax is false. Is there a way to do this?
Thank you
How To Put A Select Statement And Get Results In A Stored Procedure
Feb 2, 2008Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction.
CREATE PROCEDURE crm_contact_frequency_report
@TheYear varchar(4),@TheMonth varchar(2)
AS
SELECT
/* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 1)) AS Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 0)) AS Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode IS NULL)) AS Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS Total_All_Emails,
/* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 1)) AS Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 0)) AS Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode IS NULL)) AS Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS Total_All_Calls,
/* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 1)) AS Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 0)) AS Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode IS NULL)) AS Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS Total_All_Faxes
FROM CampaignResponse AGO
Call A Stored Procedure From A SELECT Statement
Nov 28, 2005Is there a way to call a stored procedure within a SELECT statement?Example;-----------SELECT FirstName, LastName, (EXEC UniqueID_KEYGEN @keyval output) AS UniqueIDINTO #tNewEmployeeFROM EmployeeTable-----------SELECT *FROM #tNewEmployeeThe return from the temp table would have a unique ID ready to insert into another table. Our DBA has this stored procedure to create unique ID's and is to be used on all INSERTS. I was used to having a Identity field do this for me, I don't know why we have to do it his way. Except for the reason of sequence and easily get the next record. But we don't use URL variables, only FORM or SESSION.Thanks for your help in advance.
View 2 Replies View RelatedStored Procedure In SELECT Statement?? Urgent
Sep 26, 2001I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this.
SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2
I want to write it in SQLserver. I have tried this syntax but get error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't'.
SELECT t1.id, t2.id, dbo.sp_name(t1.id, t2.id, "") FROM table1 t1, table2 t2
Can I use stored proc in this way in SQL-server?
Can A Stored Procedure Be Executed From Within A Select Statement?
Dec 2, 2005Can a stored procedure be executed from within a select statement?
Given a store procedure named: sp_proc
I wish to do something like this:
For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.
Call Stored Procedure Within SELECT Statement
Apr 17, 2004Can this be done? I want to call a stored procedure from inside a select statement. Since you can nest select statements, I thought it might be possible but I have no idea how to do it.
USE NORTHWIND
GO
CREATE TABLE tbA (
Item int NOT NULL,
Value int NOT NULL
) ON [PRIMARY]
GO
INSERT INTO tbA (Item, Value)
SELECT 1, 10 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 2
GO
CREATE PROCEDURE usp_SquareIt
@iItem int
AS
declare @iValue int
SELECT @iValue = Value FROM tbA
SELECT @iValue * @iValue AS Result
GO
SELECT Item,
EXECUTE usp_SquareIt Item AS Squared ---- can this be done
FROM tbA
GO
DROP TABLE tbA
GO
DROP PROCEDURE usp_SquareIt
GO
Any thoughts?
Mike B
Call A Stored Procedure In The Select Statement
Feb 21, 2007Is it possible to call a stored procedure in a select statement?
For example:
SELECT Buyer, Country, (exec the sp_test*) as ItemList.....
*sp_test is a stored procedure with buyer and country as the input parameters and output a concatenated item numbers (example: F12345,A1023,C40165).
Results Of SQL SELECT Statement Into Stored Procedure
Jul 23, 2005I need to run a stored procedure on all the results of a select query.For a simplified example, I'd like to be able to do something likethis:SELECT JobID, QtyToAddFROM JobsWHERE QtyToAdd > 0Then for the results of the above:EXEC sp_stockadd @JobID, @QtyToAddWhere the above line ran for every result from the above select query.Anyone able to shed some light on how to do this?Many thanks,James
View 2 Replies View RelatedSelect Statement As Input Variable In Stored Procedure?
May 6, 2015Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
Problem With The SELECT Statement Of The Stored Procedure In String Comparison.
Dec 5, 2006
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I pass a parameter to it. But if I hard
code it, it gives me the right result.
The I check if the field value of 'Email' is exactly equal to the parameter '@Email'.
The field 'Email' is varchar, and CID is integer.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn
Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com'
return
Can anyone tell me the reason for it.
Thanking you in advance
Tomy
Stored Procedure - SELECT INSERT Statement - Good Practice?
Jan 9, 2004I would like to have a stored procedure executed once a week via a DTS package. The data I would like inserted into Table_2, which is the table where the DTS is being executed on, comes from a weekly dump from Oracle into a Table_1 via another DTS package.
I would like to only import data since the last import so I was thinking of my logic to be like this:
INSERT INTO Table_2
(Field1, Field2, ... , FieldN)
VALUES (SELECT Field1, Field2, ... , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate))
Does this make sense? Or do you all suggest a different mannger of accomplishing this?
Stored Procedure Version Of SELECT Statement - Can't Understand Why It's Not Working
Apr 25, 2006Hi,
[I'm using VWD Express (ASP.NET 2.0)]
Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:
<asp:DropDownList ID="ddlContacts" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="ContactNameNumber" DataValueField="ContactID" Font-Names="Tahoma"
Font-Size="10pt" OnDataBound="ddlContacts_DataBound" OnSelectedIndexChanged="ddlContacts_SelectedIndexChanged" Width="218px">
</asp:DropDownList> <asp:Button ID="btnImport" runat="server" Text="Import" />
<asp:Button ID="Button2" runat="server" OnClick="btnNewAccount_Click" Text="New" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ICLConnectionString %>"
SelectCommand="SELECT Contacts.ContactID, Contacts.ContactLastName + ', ' + Contacts.ContactFirstName + ' - ' + Contacts.ContactNumber AS ContactNameNumber FROM Contacts INNER JOIN AccountContactLink ON Contacts.ContactID = AccountContactLink.ContactID WHERE (AccountContactLink.AccountID = @AccountID) ORDER BY ContactNameNumber">
<SelectParameters>
<asp:ControlParameter ControlID="ddlAccounts" Name="AccountID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
SQL Server 2012 :: Stored Procedure Argument For Select Where Value IN Statement
Feb 17, 2014I have a stored procedure that ends with
Select columnname from tablename order by ordercolumn
We will call that "sp_foldersOfFile". It takes 1 parameter, a fileID (int) value.
The result when I execute this from within Management Studio is a single column of 1 to n rows. I want to use these values in another stored procedure like this:
Select @userCount = COUNT(*) from permissions where UserID = @userID and
(projectid = @projectID or projectid=0) and
clientid = @clientID and
folderpermissions in (dbo.sp_FoldersOfFile(@fileID))
The Stored Procedure compiles but it does not query the folderpermissions in the selected values from the sp_FoldersOfFile procedure. I'm sure it is a syntax issue.
T-SQL (SS2K8) :: Stored Procedure And SELECT Statement Return Different Results
Dec 4, 2014I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
SQL Server 2012 :: Stored Procedure - How To Join Another Table Into Select Statement
Jan 7, 2014I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
The structure of the periods table is as follows:
[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL
The stored procedure is currently:
USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
SQL Server 2014 :: How To Call Dynamic Query Stored Procedure In Select Statement
Jul 23, 2014I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
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)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'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.
Using Conditional Statement In Stored Prcodure To Build Select Statement
Jul 20, 2005hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies View RelatedPrint A Select Statement Within A Store Procedure
May 8, 2008I want to create store procedure which will print out something like this:
Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn)
But first one print out good, as I expected. I either got nothing or the error message on second piece.
Msg 245, Level 16, State 1, Line xxx
Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int.
Any Help will be greatly appreciated.
on SQL 2005 SP1.
Here is my example code (not the real one):
Create Procedure [dbo].[makeNewString]
@myBookID
As
Declare bookID int
Declare newRowID int
Declare insertBookInfoString nvarchar(150)
select 'Declare @newBookID int'
Set insertBookInforString =
(Select 'Insert into [dbname].dbo.[gameBooks] value (' + @gameBookID + ',' + gameBookTitle +',' + ganeVolumn +')'
from [dbname].dbo.[gameBooks])
where @gameBookID=@myBookID
Print insertBookInforString
select 'SET @newRowID = Scope_Identity()'
print @newRowID
select 'Declare @newBookID int'
Set insertBookInforString =
(Select 'Insert into [dbname].dbo.[cookBooks] value (' + @cookBookID +',' + cookBookTitle +','+cookVolumn+')'
from [dbname].dbo.[cookBooks]
where @cookBookID=@myBookID)
Print insertBookInforString
first insertBookInfoString prints out fine.
But I kept this error on second part:
------------------------------
DECLARE Declare @newBookID int
(1 row(s) affected)
Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn) (this is what I want)
-----------------------------------------
SET @newBookID = Scope_Identity()
(1 row(s) affected)
Msg 245, Level 16, State 1, Line xxx
Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int.
SQL 2012 :: Store Procedure Only Output One Select Statement
May 28, 2014There are about 10 select statements in a store procedure.
All select statements are need.
Is it possible to output only the result of last select statement?
How Do You Use Data From A Select Statement As Inputs For A Store Procedure?
Dec 13, 2007How do you use data from a select statement as inputs for a store procedure?
e.g.
Code Block
Select FirstName, LastName from Student where Grade = 'A'
And I want to use all the FirstName and LastName as inputs for this store procedure
Code Block
Exec StudentOfTheMonth @FirstName = FirstName, @LastName = LastName
Thanks
Multiple Select Statement In A Stored-proc?
Mar 1, 2005I am using SQL sever 2k and C#.
There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?
What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?
I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.
stored procedure example: NorthWind database:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE dbo.getShippersAndEmployeesXML
AS
select * from Shippers for xml auto, elements
select * from Employees for xml auto, elements
RETURN @@ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
C# code example:
//set connect, build sqlcommand etc
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());
Thanks for your help.
Calling Stored Procedures In A Select Statement
Feb 26, 2004I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
View 4 Replies View RelatedHow To Use If Then Statement In A Sql Stored Procedure?
Oct 17, 2007How do you use an if then statement in a sql stored procedure? I have a table with 3 columns in it. When the tableis updated, I will only need one particular column in the database updated at a time. I will signal which column to update with an integer value(WhichColumn). So I want to key off that value to determine which column is to be updated.
Here is my pseudo code
if(WhichColumn EQUALS 1){ Update InsertEntry SET FirstColumn=Value}else if(WhichColumn EQUALS 2){ Update InsertEntry SET SecondColumn=Value}else if(WhichColumn EQUALS 3){ Update InsertEntry SET ThirdColumn=Value}
Here is a rough start to my stored procedure to incorporate the logic aboveGOCREATE PROCEDURE [dbo].[InsertEntry]( WhichColumn int Value nvarchar(500), )
Stored Procedure Using WHERE...IN Statement
Aug 10, 2005Hey all, don't know where I am going wrong on this, and maybe it's just that it can't be done. Below is the stored procedure and page code, and after that is the description of the prob*************************
SP_GetDetails
@wherestring varchar(400) ASSelect title_id, title, type, ytd_sales, pub_id from titles where title_id IN (@wherestring)*****************************This is the function that calls SP_GetDetails*****************************Public Function GetDetails(ByVal title As String) As DataViewDim DS As DataSetDim myconnection As SqlConnectionDim mycommand As SqlDataAdaptermyconnection = New SqlConnection(_ConnString)mycommand = New SqlDataAdapter("SP_GetDetails", myconnection)mycommand.SelectCommand.CommandType = CommandType.StoredProceduremycommand.SelectCommand.Parameters.Add(New SqlParameter("@wherestring", SqlDbType.VarChar, 400))mycommand.SelectCommand.Parameters("@wherestring").Value = titleDS = New DataSetTry mycommand.Fill(DS, "Details") Return DS.Tables("Details").DefaultViewCatch ex As Exception Throw exFinally myconnection.Close()End TryEnd Function****************************And this is the Function that Calls GetDetails:****************************Public Sub Get_Info(ByVal Sender As Object, ByVal e As EventArgs)Dim data As New DataLayerDim titles As String = ""Dim item As ObjectDim ltitle As StringDim i As Integer = 0For Each item In ListBox2.Items() If ListBox2.Items(i).Selected Then ltitle = ListBox2.Items(i).Value.ToString If (i > 0) And Not (titles = "") Then titles += ", '" & ltitle & "'" Else titles += "'" & ltitle & "'" End If End If ltitle = "" i += 1NextDataList1.DataSource = data.GetDetails(titles)DataList1.DataBind()End Sub****************************Problem:The DataSet is not getting any values. If I run the procedure in the QA with values that I know work, in the place of @wherestring, it works fine and I get the proper results. However if I try and give it a value (ie: Get_Info() returns "'BU1032', 'BU1111'", which are valid values, it doesn't respond) I have a feeling that its the way that the information @wherestring gets isn't formated right to be able to plug into the SP but I don't know any other way to do it...help is greatly appreciated and if you have any questions I will answer back immediately. thanks a ton. --Shred
Help With Stored Procedure/IF Statement
Aug 7, 2007Hey everyone!
I have a stored procedure that returns employee id's and how many shifts they have signed up for between 2 dates.
If they have less then 3 entries between the date range specified I would like update their status field to inactive.
So what I'm getting at is how would I go about doing an IF statement that would check through the results of my stored procedure to see who has worked less then 3 shifts and to execute another stored procedure to update their status.
Stored Procedure If Statement
May 14, 2008Hello,
I am new to this.
I am creating am trying to create a stored procedure that looks to see what group_id number a user selected and if they select no group_id number than it should return one of a specific set that I specify in my procedure, but if they pick just one group_id number I only want it to return those records. Here is my procedure. When I run in Managment Studio it says I have errors near my if, then and else.
Any Ideas?? Thanks in advance!
CREATE PROCEDURE EMRUserSecurityGroups @group_id int
If @group_id = "*All" then
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id IN ('5','20','21','23','24','25','26','27','28','29','32')
else
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id = @group_id