Stored Procedures Management - Keeping Input Parameters Updated
Dec 4, 2003
Hi everyone
I have just starting creating some stored procedures for our system and have a question related to management of these.
When using input parameters using the following syntax:
CREATE PROCEDURE sp_someInputProcedure
@Username as varchar(16)
@Password as varchar(12)
@Name as varchar(50)
@Address as varchar(60)
@Zip as int
@City as varchar(30)
...
etc.
This is all well and good, but what if I make a change in the datamodel - for instance changing a datatype or the length of a varchar - do I need to remember to manually update all stored procedures that uses these columns/variables?
Seems like a bit of a hazzle. Is there an easier way to do this?
Many thanks,
Stian Danielsen
Epizone
View 4 Replies
ADVERTISEMENT
Jan 30, 2008
Hi,
I was wondering, is it really true that in Sql Server 2005, there is no updated date -property for stored procedures? There is a created date -property, but it's not useful, because stored procedure's get updated often. This is a huge disadvantage for me, and I was really waiting for ms to fix this problem after sql server 2000, but no. Does anyone figured out any solution to this problem?
Br,
Riika
View 6 Replies
View Related
Dec 17, 2013
I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.
What would be the most proficient way of achieving this?
I had initially looked at using IF, TRY ie:
IF @SearchField= 'order_reference'
BEGIN TRY
select data
from mytables
END TRY
However I'm not sure this is the most efficient way to handle this.
View 2 Replies
View Related
May 14, 2008
Hi,
I have 2 source tables emp_ass,aprvl_status these tables are not having common column to join. and 1 target table Time_Card, i have a stored procedure with 4 input parameters, emp_ass_id,status_id,start date,end date,i am inserting data into timecard based on emp_ass_id, my week start date is sunday and end date is saterday if emp start date is sunday i am just incremnting the start date by 7 days as end date is saterday and inserting that row, if employe statrt date is other than Sunday. i am just insering start date with to reach end date saterday, this work fine when i give the input parameters, now my reqirement is i need to automate this process as i need to get new emp_ass_id which is not in target table and insert his records based on his start date and end date,
ex:
if emp_ass_id is 1001, start date 1/1/2008 and end date is 2/1/2008 then i need to insert
Uniq_Id, emp_ass_id, start_date end_date status_id
1099
1001
1/1/2008 12:00:00 AM
1/5/2008 12:00:00 AM 1
1100
1001
1/6/2008 12:00:00 AM
1/12/2008 12:00:00 AM 1
1101
1001
1/13/2008 12:00:00 AM
1/19/2008 12:00:00 AM 1
1102
1001
1/20/2008 12:00:00 AM
1/26/2008 12:00:00 AM 1
1103
1001
1/27/2008 12:00:00 AM
2/2/2008 12:00:00 AM 1
the stored procedure will insert these records if i give the input parameters, now i need to automate this process by using SSIS. please help me,i need to get emp_ass_id,start_date,end_date dynamically from source table if emp_ass_id is not in target table.
Thanks in advance.
View 9 Replies
View Related
Feb 23, 2008
Hi all,
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?
Please help and advise.
Thanks in advance,
Scott Chang
View 7 Replies
View Related
Nov 22, 2006
How many parameters can I use for a Stored Procedure.
View 4 Replies
View Related
Jun 18, 2004
Hi all,
Just a quick question regarding the use of stored procedures with parameters.
How do I call a stored procedure with a different number of parameters?
For example if:
new SqlParameter("@SectionID", "3"),
new SqlParameter("@Sessionid", Session["ID"])
SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, sqlParams);
were to change to the following on a different page:
new SqlParameter("@SectionID", "3"),
new SqlParameter("@Sessionid", Session["ID"]),
new SqlParameter("@ExtraVariable", ExtraVariable)
SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, sqlParams);
How would I handle the different number of parameters in the Stored Procedure?
cheers,
Pete
View 1 Replies
View Related
Jun 23, 1998
I want to create a stored procedure with SQL Server 6.5 that CAN take 3 parameters, all of which are optional. The declaration for the stored procedure is :
CREATE PROCEDURE BackOrdersII @CustCode varchar(10), @FromDate datetime, @ToDate datetime AS
SELECT * FROM ISO_Details
WHERE DATEDIFF(dd, fldEst_Del_Date, getdate()) > 0 AND CONVERT(char(12),fldActual_Del_Date,3)=`01/01/00` AND CONVERT(char(12),fldEst_Del_Date,3)<>`01/01/00` AND fldDeleted<>1
ORDER BY DATEDIFF(dd, fldEst_Del_Date, getdate()) DESC
How do I formulate the procedure to allow me to select from the table, keeping the current WHERE clause but adding extra items to allow the results to be further filtered depending upon which of the parameters are given to the procedure. Any one, two, or all three parameters could be given.
Sorry if this seems like a simple question but I am only just getting into using stored procedures.
Thanks Rupert
View 1 Replies
View Related
Aug 24, 2004
Have looked everywhere and cannot find the answer! So perhaps someone here can answer.
I have an Access 2000 front-end to a SQL Server 2000 database.
I know how to create Stored Procedures that receive parameters; and also how to open a Stored Procedure in the query results window using the DoCmd object. For example,
Application.DoCmd.OpenStoredProcedure "MyProc", acViewNormal, acReadOnly
Does anyone know of a way to pass parameters to Stored Procedures & open the result using the DoCmd object? Without getting a parm input dialog?
Alternatively, does anyone know of a way to open a Stored Procedure in the query results view without using the DoCmd object?
Will appreciate any guidance you can provide! Thanks!
CarlR
View 3 Replies
View Related
Jul 20, 2005
I use a stored procedure that is calling several other stored procedurewhich update or append values in several tables. All of them are storedprocedures with input parameters by which they filter rows to be updated orinserted into other tables.Filtration is based on certain actual values on forms (form with severalsubforms).My question is following: How to pass parameters to those stored proceduresthat are triggered by a button?Those stored procedures are not recordset of forms, so I can't pass it usingInput Parameters property of forms (or I can?).Thanks.Zlatko
View 1 Replies
View Related
Mar 11, 2007
i'm trying to create my first stored procedure.
i believe i've done everything correctly, the file shows up in the projects directory with the .sql extension, but the proc doesnt appear in object explorer under my database name/programmability/stored procedures.
when i execute the query, i get "Could not find stored procedure 'IsEmployeeInTable'.".
i'm running on my laptop using asp.net development server.
thanks. matt
View 1 Replies
View Related
Nov 16, 2006
Hi
My website uses GET variables a lot and i'm trying to safe guard as much as possible against SQL injection attacks. I'm trying to create permissions which will deny a user to Delete/Insert/Update various tables.
I have managed this with the tables themselves, but when using a stored procedure, the tables do not take into account the user permissions which were set for that table!
Basically, how do i stop a stored procedure from Deleting/Inserting/Updating tables? :(
many thanks
View 3 Replies
View Related
Mar 28, 2007
My colleague and I (both are newbie’s to .NET) are divided on whether to use stored procedures or parameters.
His viewpoint is, using stored procedures you are spreading the load i.e. SQL server and web server etc. This is not a good solution because it is not a portable when it has to be relocated.
Is he right?
I thought to avoid SQL injection it is best to use stored procedures but I do see his reasoning as well.
Yazzy is Very confused!!
Thanks in advance for any of your thoughts
View 2 Replies
View Related
Dec 9, 2007
How can I pass a name of a table to a stored procedure.
I want to pass the name as a parameter. The table already exists in the db.
After that, I will do
"SELECT .....
FROM @tableparameter"
What is the right way to do that?
View 3 Replies
View Related
Feb 17, 2005
Hello,
I seached around for an answer to this question but didn't have much luck. Hopefully someone can help.
I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.
Thanks for your help, Gary
Here is the web code:
Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS"))
Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)
cmdItems.CommandType = CommandType.StoredProcedure
cmdItems.Parameters.Add(New SqlParameter("@Field", SqlDbType.VarChar, 50))
cmdItems.Parameters.Add(New SqlParameter("@Value", SqlDbType.VarChar, 50))
cmdItems.Parameters("@Value").Value = txtValue.Text & "%"
cmdItems.Parameters("@Field").Value = lstField.SelectedValue
conMSS.Open()
dgdItems.DataSource = cmdItems.ExecuteReader
dgdItems.DataBind()
conMSS.Close()
Here is the stored procedure:
CREATE PROCEDURE s_ItemLookUp
@Field AS VARCHAR(50),
@Value AS VARCHAR(50)
AS
SELECT DIV_NO, DIV_NM, LN_NO, LN_DS, ITM_NO, PRD_DS, ITM_MFG_NO, VND_HFC_NM
FROM PRODUCT
WHERE @Field LIKE @Value
ORDER BY DIV_NO, LN_NO, ITM_NO
GO
View 4 Replies
View Related
Nov 18, 2005
I am creating a stored Procedure and I am getting an error which relates to DATENAME.
SELECT COUNT(*) AS calls, DATENAME(@varDate, CALLSTARTTIME) AS 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS='1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <=@EndDatesql doesn't like: DATENAME( @varDate, CallStartTime)sql works fine if I change @varDate into 'yy', 'mm', 'dd', or 'wk'Since I do not want to make 5 unique Stored Proc just because of @varDate.....Is there any way to work around this problem?
View 8 Replies
View Related
Mar 5, 1999
I need to create a SP that will accept a varying number of input parameters. A form that the user completes has a several controls that serve to narrow the number of records returned. The more parameters given, the fewer rows returned. In the past I have accomplished this by dynamically building an SQL statement. I dosen't appear possible to pass an SQL statement in a variable to a SP. Any help or pointers would be appreciated.
View 1 Replies
View Related
Feb 15, 2012
I need to set date parameters within Stored Procedures using a sql 2008 R2, with an access 2007 front end. The procedure needs to allow me to set parameters for a start date and an end date.
View 1 Replies
View Related
Jul 20, 2005
I am using SQL Server 2000. I have a table with, say, 20 columns. Ihave one procedure which updates all 20 columns at once, accepting aparameter for each column. However, I want to be able to pass anycombination of parameters and only update those columns if passed. SoI created the sp as something likecreate update_t1(@col1 int = null,@col2 int = null,@col3 int = null,....@col20 int = null)asupdate t1set col1 = @col1,col2 = @col2,col3 = @col3,.....col20 = @col20This way I can explicitly specify columns or not as I choose. Forexample I could call "exec update_t1 @col1 = 23, @col4 = 49" to updateonly the first and fourth column. Of course this will obviouslyupdate the remaining columns to null. Is there any way to identifywithin the procedure which parameters were actually specified? Ican't simply do a null check because the user could be updating thevalue to be null. Is there any way for the procedure to know theexact command that invoked it?For example, if I called "exec update_t1 @col1 = 23, @col4 = 49" Iwould want to know only col1 and col4 were specified. If I called"exec update_t1 @col1 = 23, @col4 = 49, @col17 = null" I would want toknow that col1, col4 and col17 were specified, even though col17 wasset to the default of null.
View 3 Replies
View Related
Nov 28, 2007
Greetings
Running SQL Server 2005. The developers on the project can see and edit stored procedures from within the Visual Studio IDE (via Server Explorer) but when they connect through management studio, the stored procedures do not show up at all.
Is there a seperate security setting specifically for management studio?
The user has:
The dbCreator Server Role
Is mapped to the development database as dbo with datareader/datawriter/db owner/public role
Is mapped to master reader/writer/public
Is mapped to model reader/writer public
Is mapped to msdb reader/writer public
Is mapped to tempdb reader/writer publuc
This is probably more security than the user needs, but was grasping at straws to let them edit stored procedures...
View 4 Replies
View Related
Apr 18, 2007
I've a SqlDataSource control that has stored procedures specified for each of its commands: SelectCommand, InsertCommand, UpdateCommand, DeleteCommand . And for Insert, Update and Delete, I've specified asp:parameters for each stored procedure's parameters. Now, my stored procedures all have return values, and I've successfully accessed the return values for Insert and Update, but for some reason, I'm getting very wrong results for Delete. <DeleteParameters> <asp:Parameter Name="result" Type="Int32" Direction="ReturnValue" /> <asp:Parameter Name="myID" Type="Int32" /></DeleteParameters>The moment I add my "result" with the direction ReturnValue, I instantly get a "Procedure or function <storedprocedurename> has too many arguments specified." error. I checked my SQL Profiler, and it seems that the page is passing result as an Input parameter, instead of keeping it as a ReturnValue! e.g. exec spName @myID=1, @result=NULLwhen it should be exec spName @myID=1I get the correct behavior with Update and Insert, so I'm wondering whether if this is a bug or by-design behavior or something very screwy with my computer?Help? Thoughts?
View 6 Replies
View Related
May 19, 2004
Hi,
I was just wondering if something could be explained to me.
I have the following:
1. A table which has fields with data types and lengths / sizes
2. A stored procedure for said table which also declares variables with datatype and lengths/ sizes
3. A function in written in VB .net that uses said stored procudure. The code used to add the parameters to the sql command also requires that i give a data type and size.
How come i need to specify data type and length in three different places? Am i doin it wrong?
Any information is greatly appreciated.
Thanks
Im using SQL Server 2000 with Visual Studio .Net using Visual Basic..
View 1 Replies
View Related
Jul 6, 2004
I have written a simple C# console application that create my own Stored Procedures
the code is here
----------------------------------------------------------------------------
static void Main(string[] args)
{
SqlConnection cn;
string strSql;
string strConnection;
SqlCommand cmd;
strConnection="server=(local);database=Northwind;integrated security=true;";
strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) @counter int OUTPUT AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID SET @counter=@@rowcount";
cn=new SqlConnection(strConnection);
cn.Open();
cmd=new SqlCommand(strSql,cn);
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine("Procedure Created!");
}
------------------------------------------------------------------------------------
but it has some errors becuase of my strSql
strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) @counter int OUTPUT AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID SET @counter=@@rowcount";
I mean in creating the stored procedure
if i delete the Output parameter from my stored procedure
and my strSql would be somethimg like this
strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID ";
There will be no errors
I use Visual Studio.NET 2003(full versoin)and MSDE(not Sql Server)
Could someone help me solve this problem?
Thanks and Regards.
View 1 Replies
View Related
Aug 9, 2000
Hi
I have a stored procedure some thing like this..
When I pass empty strings to both the parameters ..it is
returning all the rows from the table.
IF I pass both empty strings to @LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7),it should not select any rows from the table.. can any one suggest me as to how to accomplish this..
CREATE procedure Ceb_Phone_Book
@LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7)
AS
Select
EM010132.DEPARTMENTCODE_I,
DEPARTMENTNAME_I,
LLASTNAME_I,
FFIRSTNAME_I,
EM010132.WORKPHONE_I,
EM010132.MSTRING_I_5
FROM
EM010132 INNER JOIN HR2DEP01
ON HR2DEP01.DEPARTMENTCODE_I = EM010132.DEPARTMENTCODE_I
WHERE
INACTIVE = 0 AND
LLASTNAME_I LIKE LTRIM(RTRIM(@LLASTNAME_I)) + '%' AND
EM010132.DEPARTMENTCODE_I LIKE LTRIM(RTRIM(@DEPARTMENTCODE_I)) + '%'
ORDER BY
LLASTNAME_I,FFIRSTNAME_I
Thanks
VENU
View 3 Replies
View Related
Jun 12, 2000
I guess I'm the only one with this problem -- couldn't find anything on it in the back questions. Maybe it's a weird problem. :)
Anyway, although I'm not new to SQL, I am a bit new to stored procedures, and MS SQL Server 7. (I've been using mySQL, decent, but doesn't have many features ... )
I used some ASP and stored procedure code from 4guysfromrolla.com for session tracking through SQL Server.
I've modified most of the stored procedures so that they actually work. :)
The tables it uses are simple:
sessions: sessionid (uniqueidentifier), date_stamp (datetime), sessionipaddr(varchar(50))
sessionvalues: sessionid (uniqueidentifier), sessionvalname (varchar(100)), sessionvaldata (varchar(8000))
To answer some questions before they're asked: It's a resume database, and does need to be able to store 8000 characters at a shot. (I'm hoping 8000 is as large as it gets for this particular field.)
There's only one problem now: One of the stored procedures enters information into the sessionvalue field of the table. However, much of our data contains apostrophes ('), and we need to be able to store them. I thought that modifying the execute statement would do it, something like:
EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here' + CHAR(39) + "s some data"
This doesn't work. Indeed, even if the concatenation worked, CHAR(39) doesn't in this context.
Then I thought I'd be really clever, and try a trick from mySQL:
EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here's some data'
Naturally, that one didn't work, either. (That was a long shot, admittedly!)
This is mission-critical. Not only apostrophes, but quotes and other punctuation marks must be able to be transferred. Anyone know a way to do it?
View 3 Replies
View Related
Apr 14, 2005
I use EXEC statement to execute query in NVARCHAR format.
Query is constructed using WHILE loop. Lenght of parameter @query in limited on 4000 ch. But my parameter is longer.
Data types ntext and text can't be used for parameters.
Any idea?
View 1 Replies
View Related
Jul 20, 2005
I'm trying to pass parameters to an extended stored procedure, to noavail. I would like to pass two integers to the dll and I have thefollowing three snippets:1. The C++ portion of the dll:....declspec(dllexport) int myAddNumbers(int m, int n)....2. The creation of the extended stored procedure:EXEC sp_addextendedproc myAddNumbers , 'foodll.dll';3. The usage:create function TestFunction()returns integerasbegindeclare @rc integerexec @rc = myAddNumbersreturn (@rc)endHow do any of the above three things need to be modified in order tomake this work?Thanks!!!
View 1 Replies
View Related
Dec 20, 2007
Hi,
When I execute a stored procedure from an OLE DB Command transformation, where the sp takes a parameter and RetainSameConnection=TRUE and DelayValidation=TRUE are set, I get the error
"Syntax error, permission violation, or other nonspecific error"
If I take out the param or set RetainSameConnection=FALSE on the connection, all is fine again?
Has anyone has come across this?
Cheers
View 3 Replies
View Related
Jun 6, 2007
I have this SP:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetSessionInformation]
@CustomerID int,
@Success bit OUTPUT,
@Email VarChar(55) OUTPUT,
@FirstName VarChar(55) OUTPUT,
@LastName VarChar(50) OUTPUT,
@PhoneNumber VarChar(50) OUTPUT,
@CompanyName VarChar(50) OUTPUT
AS
SET NOCOUNT ON
DECLARE @UserKey AS int
SELECT @CustomerID = CustomerID
FROM Customers
WHERE CustomerID = @CustomerID
IF @CustomerID IS NULL
BEGIN
SET @Success = 0
END
ELSE
BEGIN
SET @Success = 1
END
BEGIN
SELECT customerID, Email, FirstName, LastName, PhoneNumber, CompanyName
FROM Customers
WHERE CustomerID = @UserKey
How do I test it in management studio?
When I run a EXECUTE GetSessionInformation 56
I get this error:
Procedure 'GetSessionInformation' expects parameter '@Success', which was not supplied.
Thanks for any help!
View 2 Replies
View Related
Sep 3, 2007
I am learning T SQL and SQL queries and have limited VB knowledge, and have a some simple queries to run on a table with parameters, and would like verification of the proposed methodology and suggestions.
Simply put, I have a [Transactions] table with columns [Price], [Ticker], [TransDate], [TransType] and calculated columns for [Days] and [Profit].
There are two parameters, [@Dys] (to query a the table for transactions within a certain period[Days = 30] and [@TT] to query only the closed transactions ie... [TransType='C']
I have been studying Stored Procedures and will be writing a Stored Procedure, but need verification if the following will work... Getting the SUM and AVG calcluations for the fields above is not a problem but I need to display SUM and AVG information also for those transactions where [Profit >0] and [Profit <0], which is easy enough by creating a subquery. But the problem is:
1. If I use a SubQuery for [Profit <0] and for [Profit>0], can I create an alias for [Count(*)] (to get a row or transaction count for each, and then divide that into the Total [Count(*)] alias for the Transactions table to get a value for % profitable or Probability (% total Profitable trades versus % total Unprofitable trades)?
2. Or, do I need to create either temporary tables or views to have 3 distinct tables (1 table for Transactoins and 2 temp or Views for [Profit >0] and [Profit <0])?
Any suggestions and advice or examples on how to do this would be appreciated.
Craig
My questions are:
View 2 Replies
View Related
Dec 10, 2007
Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.
I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).
On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:
ALTER PROCEDURE [dbo].[S_SPROC] ( @ID int, @TYPE CHAR(10), @NAME CHAR(20) OUTPUT )AS
SELECT @NAME = NAME FROM SPROCSWHERE [ID] = @IDAND [TYPE] = @TYPE
Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.Has anyone done something similar before, or could point me in the right direction to solving this problem.
ThanksPhil
View 8 Replies
View Related
Sep 15, 2005
I've created a search page in my asp.net app that allows the user to enter optional parameters to narrow down the result set. It looks something like:Find all parts where: manuafacturer: <dropdownlist>ANY | manufacturer 1 |... </dropdownlist> model: <dropdownlist>ANY | model 1 |... </dropdownlist> cost: between <textbox> and <textbox> dollarsCurrently I create the SQL command on the fly building the WHERE based on what the user selects. For example if in the form above they select manufacturer = manufacturer1 model = ANY cost = between 10 and 15the WHERE string is ... WHERE manufacturer='manufacturer1' AND cost BETWEEN 10 AND 15Since the user doesn't care about model I leave it out of the WHERE. OK so here is my question. I want to move my queries to strored procedures however I'm not sure how to create the query since it changes based on what the user enters. Using the example above I'm assuming I can create one query with 4 parameters however what value would I use for ANY? parameter1 (manufacturer) = "manufacturer1" parameter2 (model) = ??? parameter3 (price low) = 10 parameter4 (proce high) = 15I see there is an ANY operator in T-SQL but it doesn't look like the right thing to use. Should I use LIKE '%'? Seems that using LIKE would result in addition overhead.ThanksSimon
View 2 Replies
View Related
Jun 24, 2015
Is there a way to combine 2 stored procedures with a different set off parameters.
Basically my 1st stored procedure has the following parameters:
1.@PlanID
2.@FinancialYearID
3.@RangetypeID
My second stored proc has the following:
1.@FinancialYearID
2.@IndicatorID
3.@VersionID
I have researched and so far nothing seems to be working. There is a conflict between the FinancialYearID of the 1st and 2nd stored procs.
My overall result is the combination of the 1st and 2nd storedprocs in 1.
View 9 Replies
View Related
