Stored Procedures And Optional Parameters

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


ADVERTISEMENT

Using Stored Procedures/parameters When WHERE String Has Optional Conditions

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

Optional WHERE In Stored Procedures

Aug 30, 2004

Hello guys,
I want to implement a searching with a stored procedure. As an example I have a table like
> tableA ; Id,Name,Surname,Gender(bit) 0 -Male , 1 - Female

If i want to choose all records that are male, I could simply have a query like this :
SELECT * FROM tableA WHERE Gender = 0
and vice versa for female.
If i want to choose all rows in the table i should omit where clause.
The problem is , when i use a stored procedure i should send a parameter. And i dont know if there is a possible way to to do this. When i try such procedure :
CREATE astoredprc
(@gender bit)
AS
SELECT * FROM tableA WHERE Gender = @Gender
GO
--
In such prc. i can only get rows with a where clause. What if i want to use the same procedure with all rows and omiting where. I have many fields like this and I m unsure if I should use a text query or not ...
Thanks for all of u from now on

View 4 Replies View Related

Optional Parameters In A Stored Proc?

May 19, 2004

Title speaks for itself really.
Is it possible to write a stored proc with optional parameters?
For example consider the following SELECT

SELECT FLD1, FLD2 FLD3 FROM TBL1

I'd like to add optional parameters to that statement so that if they wanted to narrow down the results by providing criteria for some fields they could - but didn't have to.

Is this possible?

View 4 Replies View Related

Optional Parameters For CLR Stored Procedure

Oct 2, 2007

How do I specify optional parameters when writing CLR based Stored Procedures in C#?

View 16 Replies View Related

Optional Parameters In A Stored Procedure

Jan 7, 2008

Hi all,

Can anyone please tell me what is the best way to handle optional parameters in a stored procedure which will ensure a good execution plan? I ask this as I have to create a stored procedure with six optional parameters and I'm getting a little concerned that, apart from the parameter issue, I'm going to have a lot of if else statements. Is there an easy way of doing all this?

Thanks

View 5 Replies View Related

SqlDataSource And Optional Stored Procedure Parameters

Oct 12, 2006

Everyone,I have an application that pulls a list of employees into a web form.  I use the same stored procedure to search for employees by various demographic characteristics, and have set all of these search parameters to be optional in the stored procedure.Everything works fine when I pull the entire list (without declaring any select parameters).  However, on my Search page, I have declared a number of SelectParameter (ControlParameters, to be specific) objects and mapped each one to its respective control (text boxes and a checkbox).  When I attempt a search, the SqlDataSource does not select any records unless I specify an argument for ALL of the search parameters.  If I omit even a single search value, the page just posts back and does not pull any information from the database.  I have verified with SQL Profiler that it is not attempting to hit the database.I have experimented with various declarations in the ControlParameter object, including the DefaultValue and ConvertEmptyStringToNull items.  If I specify a non-empty string as a DefaultValue for all of the parameters, it will perform the query, but it does not query if I use an empty string as a default value - unless my user specifies a value in the form. Any ideas?ThanksTim

View 1 Replies View Related

Stored Procedure Optional Search Parameters

May 18, 2008

Hi I want to give the user the ability to search based on a number of criteria, however i do not know how to build my statement properly this is what i have so far;
ALTER PROCEDURE [dbo].[stream_StoreFind]
-- Add the parameters for the stored procedure here
@StoreName varchar(250),@subCategoryID INT
AS
SELECT Stores.StoreName ,StoreCategories.storeIDFROM Stores INNER JOIN
StoreCategoriesON
Stores.storeID = StoreCategories.storeID INNER JOIN
SubCategories ON
StoreCategories.subCategoryID = SubCategories.subCategoryID WHERE
 
My problem is how will i pass the parameters into the statement, taking into fact that sometimes they may be optional. Thank you 

View 12 Replies View Related

Stored Proc Update Optional Parameters

Dec 3, 2003

I have a stored procedure that updates about a dozen rows.

I have some overloaded functions that I should update different combinations of the rows - 1 function might update 3 rows, another 7 rows.

Do I have to write a stored procedure for each function or I can I handle it in the Stored Procedure. I realise I can have default values but I the default values could overwrite actual data if the values are not supplied but have been previously written.

Many thanks for any guidance.

Simon

View 5 Replies View Related

Optional Parameters Passed To Stored Procedure

Jul 23, 2005

In which system table the information about the optional parameters passed to stored procedure are stored.I know about the tbl_columns and all that stuff. From where can i can come to know the parameter is mandatory or optional.--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Report Based On Optional Parameters From Stored Procedure

Jan 12, 2008



I have the following stored procedure:



Code Block
CREATE PROCEDURE udsp_td_queryowner
@state varchar(10) = NULL,
@businesstype varchar(20) = NULL,
@size int = NULL,
@sortorder varchar(20) = 'state'
AS
SELECT gl_t.state AS [State],
gl_t.business_type AS [Business Type],
gl_t.lowsize AS [Low Size],
gl_t.highsize AS [High Size],
e.InternetAddress AS [Owner]
FROM gl_territory gl_t JOIN employee e ON gl_t.employeenumber = e.EmployeeNumber
WHERE state = COALESCE(@state, state) AND
business_type = COALESCE(@businesstype, business_type) AND
COALESCE(@size, lowsize, highsize) between lowsize AND highsize
ORDER BY CASE WHEN @sortorder = 'state' THEN gl_t.state
WHEN @sortorder = 'business type' THEN gl_t.business_type
WHEN @sortorder = 'owner' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END ASC,
CASE WHEN @sortorder = 'low size' THEN gl_t.lowsize
WHEN @sortorder = 'high size' THEN gl_t.highsize
END ASC,
CASE WHEN @sortorder = 'statedesc' THEN gl_t.state
WHEN @sortorder = 'business typedesc' THEN gl_t.business_type
WHEN @sortorder = 'ownerdesc' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END DESC,
CASE WHEN @sortorder = 'low sizedesc' THEN gl_t.lowsize
WHEN @sortorder = 'high sizedesc' THEN gl_t.highsize
END DESC





What it allows me to do is enter in any number of the variables when running the stored procedure. For example, EXECUTE udsp_td_queryowner @state = 'IA' would give me everything in the table in the state of IA regardless of the other field values. Likewise, if I ran EXECUTE udsp_td_queryowner @state = 'KY', @size = 15 it would return the records that are in KY and the size of 15 is in the range of the low and high value.


If I run the first example in Query Analyzer I get 53 records from the data I have. It returns every record that has IA as the state. I run the same thing in Reporting Services and all I get is 3 records. Just the 3 records for the state of IA where the business type is either null or blank (I can't tell.) If I allow all of the variables to accept Nulls then it returns the correct data. However, I would like to find a better alternative because when the report is run it returns all of the records in the table initially and if they user wants to enter in any parameters they have to toggle off the null box for the corresponding paramter.


Any ideas?

View 12 Replies View Related

Stored Procedures And Parameters

Nov 22, 2006

How many parameters can I use for a Stored Procedure.
 

View 4 Replies View Related

Using Stored Procedures With Parameters

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

Stored Procedures And Parameters

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

Stored Procedures And Parameters

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

Stored Procedures With Parameters (in .adp)

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

Which Is A Better Stored Procedures Verse Parameters

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

Table Parameters In Stored Procedures

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

Passing Parameters To Stored Procedures

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

Putting @Parameters In Stored Procedures

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

Set Date Parameters Within Stored Procedures?

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

Identifying Specified Parameters In Stored Procedures

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

DeleteCommand, Stored Procedures, And ReturnValue Parameters = Can't Be Done?

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

SQL Server Stored Procedures, Tables And Parameters

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

Some Problems With Output Parameters In Stored Procedures

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

Passing Empty Parameters To Stored Procedures

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

Stored Procedures, String Concatenation In Parameters

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

Linited Lenght For Parameters In Stored Procedures

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

Passing Parameters To Extended Stored Procedures

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

OLE DB Command Transformation, Stored Procedures + Parameters

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

Optional Parameters

Sep 15, 2005

Is there an option in a stored procedure whereby a parameter can be flagged as optional? I have a stored procedure with 2 parameters, Product and Date, and I would like to be able to just pass the Product, or pass Product and Date from an Access project. Is this possible?

View 5 Replies View Related

Learning Stored Procedures For Querying Tables With Parameters

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

Problems Calling A Stored Procedures Depending On Parameters

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







Copyrights 2005-15 www.BigResource.com, All rights reserved