I have an sp that I would like to us the data from one column to use as a parameter for the following Select Statement
[Code]....
need to set the @customerID parameter = customer_data.CustomerID from the first Select Statement
Just quick question, is it possible to Use a Stored Procedure which has a parameter in a select statement,, I mean something like the following
SELECT A, B
FROM Something
-- Something is the name of the Stored Procedure which take date parameter
Ive been banging my head against the wall for the last 2 days with this query I am trying to produce. I have an application that creates requests and these requests have status codes dependant on how far along in the process they are : new request, awaiting approval, approved, in progress, complete When a request is moved to the next status, it Inserts a row into an auditing table, so we can track who moved it onto the next status, along with a timestamp. There are Service Level Agreements in place that mean that a request that is currently 'In Progress' must be actioned to 'Complete' within 5 days. I have been asked to create a query that will return all records that fall outside of this SLA. My main problem is trying to Select the time from the audit log for 'In Progress' and 'Complete' status codes. It would be simple if i only had to find one of the times, as i could add a "WHERE StatusCode = 'complete' clause to the query My Audit table is structured as follows:
[RequestID] [Request Status] [UserID] [CreateDate]
So far I have been successful in querying the table by using the DATEDIFF() function as below:
SELECT
DateDiff
(day,
( SELECT
top 1 CreateDate
FROM audit_tblRequest
WHERE RequestStatus
= 'In Progress'
AND RequestID
=
@RequestID Order
by CreateDate
DESC)
,(SELECT
top 1 CreateDate
FROM audit_tblRequestWHERE RequestStatus
= 90AND RequestID
=@RequestID
Order
by CreateDate
DESC)
)
This will return the difference in days to me, however, i cannot figure out how to integrate this into a query that will be run across all RequestIDs. I was wondering if there was any way to bundle the DateDiff function into a stored Procedure and be able to call it For Each RequestID in the result set I would need to pass the requestID as a parameter, and also add some If Else logic to only return records outside of the 5 Day SLA. Im sure there are some patches in the above logic, but my brain is about to explode and I needed to type it all out before calling it a day for today.
how can I create stored procedure and write my select statement in it, I know how to create dataset then put type stored procedure and assign it to the sp ... what I want is writing my sp... how can I make it ?
check the following code, this is what I want to write put I don't know where or how !
CREATE STORED PROCEDURE SP_CATEGORY
@CATEGORY VARCHAR(30)
AS
BEGIN
SELECT LATIN_NAME, ENGLISH_NAME, ARABIC_NAME, CATEGORY
FROM FLORA, CATEGORY_LIST
WHERE FLORA.CATEGORY=CATEGORY_LIST.CATEGORY_NAME AND CATEGORY_LIST.CATEGORY_NAME IN (SELECT * FROM SPLITLIST(@CATEGORY, ','))
END
where can I write this code ?!
I am executing stored procedure using session.CreateSqlQueries but it is giving me exception as {"SQL queries do not currently support returning aliases"}.
I tried doing following also but it is not working for me
[code]....
Is there any option to use loop statement in MS SQL Stored Procedure?
Suppose I have two tables Table-A and Table-B
Table-A
ID
Name
Phone
1
X
12354
2
Y
3689
3
Z
2578
Table-B
ID
Name
Phone
I want to insert all the values of Table-A into Table-B by using Stored Procedure
I am using MS SQL 2005
I have this stored procedure which check a collection and according to that return a result.
The problem is I want to check if a number is between 10-20 then do something
How on earth you checking something like that? I know > and < etc.. but checking if a value is a number between 10-20?
how can i pass a session variable in a C# web form to a stored procedure..?
View 1 RepliesI have stored procedure that increments a number. I am supposed to use the newly generated number and insert it into a table. My question is how do I execute the stored procedure, with a hardcoded value of 3 that it expects as a paramter, receive the number it generates as the output parameter and use it in the insert statement.
Here is the stored procedure code:
CREATE PROCEDURE [dbo].[Generate]
@myid int,
@next_no int output
AS
update mytable
set mynumber=mynumber + 1, @next_no = number
where myid=@myid
RETURN
Here is my insert statement, that I tried and it failed:
EXEC @GetNo = Generate '3'
Insert into customers (custid, name, phone ) values
( @GetNo, 'MSFT', '800-345-5678')
I have a Datatable and I want to send it in SqlServer as Parameter in stored procedure.
1. How i send datatable
2. How I use that datetable in sqlserver to read its data.
How do you call a stored procedure using C# .NET 3.5 that contains a parameter that is of type hierarchyid in SQL Server 2008?
A basic outline of my current code is as follows:
SqlConnection conn =
new
SqlConnection(DatabaseConnectionString);
SqlCommand StoredProcedureCommand =
new
SqlCommand("AddUser", conn);
StoredProcedureCommand.CommandType =CommandType.StoredProcedure;
SqlParameter ParamUsername = StoredProcedureCommand.Parameters.Add("@Username",
SqlDbType.NVarChar);
SqlParameter ParamPassword = StoredProcedureCommand.Parameters.Add("@Password",
SqlDbType.NVarChar);
SqlParameter ParamMasterNode = StoredProcedureCommand.Parameters.Add("@MasterNode",
SqlDbType.Text);
// HierarchyID type
ParamDynamboUsername.Value = Username;
ParamPassword.Value = Password;
ParamMasterNode.Value = "/";
conn.Open();
StoredProcedureCommand.ExecuteNonQuery();
conn.Close();
ALTER procedure [dbo].[Sample2]
@Col as nvarchar(1000),
@Value as nvarchar(1000)
[code]...
I am using DAAB with stored procedures. I use stored procedures more then once if possible. If I add a parameter later in stored procedure, I have to supply value for this in functions where it is even not being used as well, other wise it shows an error that number of parameters are not same. if I add parameter in last of parameter list in stored procedure and set a default value for it and I may have not to provide value for this from functions where I am not using it.
View 1 RepliesI spent about 60 minutes sifting through search engine listings trying to figure this out and couldn't find an answer, so I decided to make this post for others.
Situation: Using a Stored Procedure to insert a row and return the new row ID, explained here. In my case, I used the Idenity_Scope to return the new row, like so:
[Code]....
Next, following steps as described in link above, went into the dataset XSD page, configured the table adapter to use this stored procedure. By default, it wanted my return value to be set to some value, but I set to allow Null (makes sense, since I don't want to pass the value in, I want to get it out, so it should be null to begin with).
Problem: Where I ran into an issue was in the method to call this stored procedure. The intellisense displayed the prototype it was expecting, basicly like this:
[Code]....
i want to return two output parameter n temp table from sql server stored procedure
View 6 RepliesIs it possible to add multiple select statements in a single stored procedure. The select statements are getting data from different tables. If yes, could anybody provide an example in adding multiple select statements, which retrieve data from different tables in a stored procedure.
View 3 RepliesI'm using VS 2008 and SQL 2008.
I have created the tables and the stored procedures in SQL 2008.
In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.
Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.
First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete).
I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.
I am rather confused as how or where to declare this variable in asp.net.
[Code]....
Whenever i put this in my sqldatasource for it is asking me to Define Parameters:
The wizard has detected one or more parameters in your SELECT statement. For each parameter in the Select Statement, choose a source for the parameter's value.
This is surareddy. i nead some small clarification in the "Stored Procedure"
how to convert the oracle Stored Procedure to sqlserver2005/2008 Stored Procedure.
right now i am enhancing the project that project already developed the oracle Stored Procedure. now our company is using sqlserver 2005/2008.
how to convert the Oracle Stored Procedure to sqlserver 2005 Stored Procedure
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
View 4 RepliesI will declarate a attribute, but it gives me an error
This query works fine and returns 1 record from type int:
SELECT TOP 1 DataObjectVersionID
FROM tblDataObjectVersionPropertyValueText
WHERE PropValue like CAST('00010281' AS ntext)
ORDER BY DataObjectVersionID DESC
And when I will declarate a attribute/parameter it gives me an error:
DECLARE @dataObjectVersionId INT
SET @dataObjectVersionId = SELECT TOP 1 DataObjectVersionID
FROM tblDataObjectVersionPropertyValueText
WHERE PropValue like CAST('00010281' AS ntext)
ORDER BY DataObjectVersionID DESC
Error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
I have a requirement where I need to declare a Sql Statement dynamically in a cusror. When I declare dynamic Sql Statement and execute the SP, it throws an error message. The Cursor is not recognizing the dynamic Sql command.
As per the requirement, the sql statement will change according to the input parameter and it can't be declared as a static sql as shown below. Can anyone tell me, how this can be achieved any other alternative to do?
I´m in need to export datagrids to excel. I found a good solution in C#. I´m a newbie at VB.net. I have got it to work but I´m not familar to set the SQL connection and bind data in C#.
I need to declare my query with a calendar parameter but I have no idea how this should be done in this code.
Either, how do I set parameters into the C# code or how can I set a SQLDATASOURCE in the ASPX file and still run all code.
ASPX CODE
[Code]....
And CS
[Code]....
Does anybody if it is possible that a stored procedure returns rows which is the result of the execution of another sp? Something like..
[Code]....
i want to return output parameter from 1 storeprocedure. into another stored procedure.
View 7 Replies