I have few inline sql statements with some arrays and if loops. I want to change them all into one stored procedure in SQL server 2005.
As i am learning SQL, i have got no idea about how it should be done with arrays and if loops. Is that possible to send arrays into stored procs or is there any other solution.
[code]....
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 ?!
split off from [URL]
how do i do this in stored procedure .. can you give me ane xample using if than else condition in stored procedure?
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]....
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.
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?
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
The following stored procedure updates the value in LeaveTransaction table.
[code].,...
My Question here:
I have written the above trigger,which would update the value for the corresponding records in another table ie) LeaveCumulative table. The trigger is not updating the value in the designated rows in the leavecumulative table. Whereas when I excute the same update command separately in the Query Editor window the trigger is working fine.
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 RepliesChange user's password via stored procedure in ASPNETDB. Changed to another post [URL]
View 1 RepliesI could probably figure this out if I tried to, but I have been working so long on code, I'm a little fried
I have a stored procedure, and I want to execute another stored procedure during a time period of lets say 1/1/2011 to 12/31/2011
How Would I accomplish this?
if there is any easier way to synchronize the TypedDataSets with the chages made to backend Table / Stored Procedure schema. I use Typed DataSets in my project extensively and I found them very useful and easier to code, but difficult to maintain
The difficuly I have always faced is whenever there is a change in a backend database table structure or stored procedure that is linked to any of the Typed DataSet in our project requires to recreate whole DataSet again by scrapping the old one. The backend changes will not be reflected unless and untill you recreate the whole stuff again.
Is there any easier way to synchronize those typed datasets with the changes made to backend database schema or any other workaround that will not required to recreate the whole DataSet again.
I have one repeater on page which has his own sql data source. I've placed 2 dropdown lists on the page. The repeater has jQuery DataTable plugin. With 2 dropdownlist I wish to change the sql stored procedures so when each will be selected the data in repeater will change. When user select the first there will be only data (users) according to selected item and in the second the same. I wrote 2 simple functions for changing stored procedure of repeater and rebiding it. Everything works but when I try the second one and then the first I always get warning about error "Procedure or function .... has too many arguments specified".
I think that when I select the first one the second function is still called although I don't call it.
[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 RepliesI am trying to do an inline IF statement inside a asp:Repeater control to add a class to the first item, but I can't quite seem to figure it out.
Basically the code I have right now that is not working but should give an idea of what I'm "trying" to do looks like this.
<asp:Repeater ID="rptrTabRepeater" runat="server">
<ItemTemplate>
<div class="tab <%= If Container.ItemIndex = 0 Then %>highlight<% End If%>">
'Other stuff here
</div>
</ItemTemplate>
</asp:Repeater>
I have tried using the OnItemDataBound event but the delegate interface cannot return a value. If I'm going to do anything from a code-behind function really it would just need to be an "echo" kind of function which I wasn't quite sure how to get the item index in a code behind function. If I could do something inline like my example that would be the best solution for me.
EDIT: The compile error I am getting is:
Compiler Error Message: BC30201: Expression expected.
I have a datalist which acts like a table and displays certain values. I'm trying to display a boolean value as yes or no as opposed to true or false. This is what my current line looks like, is there any way to use an inline If statement to get this to work?
[Code]....
How can I make this show yes or no instead of true or false?
I am using C# and ASP.NET
I'm trying to display an image inside a ListView control based on the value of a databound property. I've tried two methods of doing this (one at a time) and both returned errors of "The server tag is not well formed". Consider the code below.
<ItemTemplate>
<div class="left">
<!-- Method 1 -->
<img src="media-play-button.png" alt="Play" class="mediaplay noborder" runat="server" visible="<%# Eval("MediaType").ToString() == "video" %>" />
<!-- Method 2 -->.........
I'm trying to get a Razor conditional to write a few characters into an HTML page, but I can't figure out the syntax to pass the text characters straight to the screen. This bit of code works correctly:
@{if(sortColumn=="BbConvTerm"){<p>ASDF</p>}}
But I don't want those <p></p> tags. I just want it to insert the "ASDF" characters into HTML. For example:[doesn't work] @{if(sortColumn=="BbConvTerm"){ASDF}} [doesn't work] @{if(sortColumn=="BbConvTerm"){new HtmlString("ASDF")}} [doesn't work] @{if(sortColumn=="BbConvTerm"){Html.Raw("ASDF")}}
[Code]....
i have a stored procedure with this values and i need just to make a button that will send something to "Equip" column,how to do it? write the values on my webpage and make a button to exec the procedu
I am trying to create the following stored procedure in sql server Lat and Lng are the parameters being passed from c# code behind .But I am not able to create this stored procedureit indicates with error saying undefined column name Lat,Lng
CREATE FUNCTION spherical_distance(@a float, @b float, @c float)
RETURNS float
AS
BEGIN
RETURN ( 6371 * ACOS( COS( (@a/@b) ) * COS( (Lat/@b) ) * COS( ( Lng/@b ) - (@c/@b) ) + SIN( @a/@b ) * SIN( Lat/@b ) ) )
END
sqlda.SelectCommand.CommandText = "select *, spherical_distance( Lat, 57.2958, Lng) as distance
from business
[code]...
I want to apply other stored procedure select query on result of first stored procedure.
View 1 Replies