Retrieving Default Values For Parameters In A Stored Procedure.

Jul 23, 2005

I'm generating a list of parameters needed by stored procedures, and
I'd like to know which ones have default values assigned to them.
To retrieve the parameter information I use:



sp_sproc_columns @Procedure_Name='InsertUser''



However, the column that is supposed to give the default value,
'COLUMN_DEF' always returns as NULL, even when that column has a
default value assigned to it.
i.e.

CREATE PROCEDURE InsertUser
@UserID INT = 10,
.....



And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.

Does anyone know what I'm doing wrong and how I can retrieve the default value?

Thanks

View 1 Replies


ADVERTISEMENT

Retrieving Values From Stored Procedure Using FormView && SqlDataSource

Mar 3, 2008

Please excuse me if this question has been asked before; I couldn’t find it.  Perhaps someone could point me to the solution.
A few years ago I wrote an order-entry application in classic ASP that I am now re-writing in ASP.NET 2.0.  I am having very good success however I can’t figure out how to retrieve data from a stored procedure.
I am using the FormView & SqlDataSource controls with a stored procedure to insert items into an order.  Every time an item is inserted into the order the stored procedure does all kinds of business logic to figure out if the order qualifies for pricing promotions, free shipping, etc.  If something happens that the user needs to know about the stored procedure returns a message with this line (last line in the SP)
 SELECT @MessageCode AS MessageCode, @MessageText AS MessageText 
I need to retrieve both the @MessageCode and the @MessageText values in my application.  In classic ASP I retrieved these values by executing the SP with a MyRecordset.Open() and accessing them as normal fields in the recordset.
In ASP.NET I have specified the SP as the InsertCommand for the SqlDataSource control.  I have supplied all the parameters required by my SP and told the SqlDataSource that the insert command is a “StoredProcedureâ€?.  Everything actuly works just fine.  The items are added to the order and the business logic in the SP works fine.  I just have no way of telling the user that something important has happened.
Does anyone know how I can pickup these values so that I can show them to my users?  Bassicly if @MessageCode <> 0 I want to show @MessageText on the screen.

View 10 Replies View Related

Default Stored Procedure Parameters

Jul 23, 2005

Hello, does anyone know how to determine (within the stored procedure)whether a parameter has been passed explicitly to a stored procedure asopposed to a parameter that has taken on the default value.Many thanksPhil

View 4 Replies View Related

How To Pass Values To A Stored Procedure Parameters By Using Script Task

Nov 27, 2007



Hi All,

I have One package that it contains one Execute SQL task in that i have placed a Stored procedure .
Now i want to pass values to Stored procedure parameters from a databse table by dynamically .For this i am trying to use " Script task "
How can i pass that table column values to that stores procedure thru using Script Task?

Regards,
Maruthi..

View 3 Replies View Related

SQL 2012 :: Find Out Values Of Parameters When SP Is Executed With Default Values?

May 30, 2014

I am working with SP. How can we find out values of parameters when the SP is executed with the default values?

View 9 Replies View Related

Default Values Does Not Include All The Values (Cascading Parameters)

Mar 18, 2007

A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.

One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).

If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with  Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.

What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.

 

View 8 Replies View Related

Can You Force A Refresh Of The Default Values Of Your Parameters?

Aug 1, 2006

This is regarding Report Services 2005 designing the report in Visual Studio.

Currently I am using a query to populate 2 date selectors in the parameter section at the top of a report.  The start and end dates are determined by a query which returns the values based on yet another parameter.  If I change the value of the paramter which is supposed to be used by the date selectors they don't seem to feel the need to re-calc themselves.  I have 3 other dependent param's in the same report which behave reasonably.

Has anyone seen this before or is there way to somehow force a parameter re-caluclation?

 

jSiegel

 

View 1 Replies View Related

Default Values For Parameters Automatically Generates Report

Apr 26, 2007

I have created a report, and am setting default values for the parameters. Once the default parameters is set, the report automatically generates. Is there a way to make it not automatically generate the report? We are setting the parameters with values that will get the end user the most recent data, but alot of times, they will want to pull older data as well. We don't want them to have to wait for the report to automatically generate before they can change the parameter values

View 1 Replies View Related

Reports Running Automatically When All Parameters Had Default Values

Nov 10, 2006

If all the parameters for a report have default values, the report runs automatically as soon as you open it. Is there a way to prevent this?

View 9 Replies View Related

Help Retrieving Values From 2 Similar Stored Procs

May 1, 2006

I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second.  Now I want to obtain the results from both like this:

ProjFee   ProjGross  DailyRunRate  Var1  InHouse1  InHouse2  GrossGoal  Group Name  PostedAmount
---------------------------------------------------------------------------------------------------------------------------
Row# 1 from current stored proc
Row # 2 from called stored proc

so something like this I should get in the end for example when both results are combined:

ProjFee   ProjGross  DailyRunRate  Var1  InHouse1  InHouse2  GrossGoal  Group Name  PostedAmount
---------------------------------------------------------------------------------------------------------------------------
100000   33455        200                 300    345555      4455555    5666666     Arizona           56000
103400   22455        900                 700    777555      3333555    5444666     Illinois            660000


The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.

I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.

What is the best approach to get the desired 2 rows back that I need in that fasion?

Here is the code for the 2 stored procs (very long so I will post as as links):

http://www.webfound.net/CurrentMonthCollections_AZ.txt
http://www.webfound.net/CurrentMonthCollections_IL.txt

Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee   ProjGross   DailyRunRate   Var1   InHouse1   InHouse2   GrossGoal   Var1   PostedAmount
) from both stored procs.

I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows

View 7 Replies View Related

Retrieving Scalar Or Calculated Values From Stored Procedures With C#

Feb 4, 2007

I am trying to build an Sql page hit provider.  I am having trouble getting a count back from the database.  If I use ExecuteScalar it doesn't see any value in the returned R1C1.  If I use ExecuteNonQuery with a @ReturnValue, the return value parameter value is always zero.  Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#.  My table has rvPathName, userName and a date.  I have the AddWebPageHit method working so I know data connection and sql support code in provider is working.  I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#.  Any help with this will be greatly appreciated.

View 5 Replies View Related

Retrieving Values From Print Statemens In Stored Proc In Sql Into Asp.net Page

Sep 3, 2007

 Hi allI hv made a stored procedure which printsvarious messages using Print statement(shown in bold)------------------------------------------------------------------------------ .....if (@current_date<@ed) and (@current_date>@sd)    begin    print 'Date Lies Between Boundary Limits'    select * from membership where uid=@uid    end    else    begin    if(@pipe=1)            begin                if(@plan_id=1)                begin                 print 'Monthly Plan Activated'                update membership set   start_date=@opt_sd,end_date=DateAdd(M,1,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=20 where uid=@uid                select * from membership where uid=@uid                end                else                    begin                    print 'Weekly Plan Activated'                    update membership set start_date=@opt_sd,end_date=DateAdd(D,7,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=10 where uid=@uid                    select * from membership where uid=@uid                    end            end    end --------------Now I want to retrieve the messages disp by these Print statements in my asp.net page where i m calling this stored proc.Pls suggest RegardsMunish 

View 2 Replies View Related

Retrieving Data Using A Stored Procedure

Mar 31, 2007

I have created the following stored procedure and tried to retrieve it's output value in C#, however I am getting exceptions. Can anyone tell me what I am doing wrong? Thanks!  1 ALTER PROCEDURE [dbo].[GetCustomerById]
2
3 @CustId NCHAR(5),
4 @CustomerName NVARCHAR(50) OUTPUT
5
6 AS
7 BEGIN
8
9 SELECT @CustomerName = ContactName
10 FROM Customers
11 WHERE CustomerId = @CustId
12
13 END
14
15 RETURN
16
17
18
19
20
21
22 SqlConnection conn = GetConnection(); //retrieves a new SqlConnection
23 SqlCommand cmd = new SqlCommand();
24 cmd.Connection = conn;
25 cmd.CommandType = CommandType.StoredProcedure;
26 cmd.CommandText = "GetCustomerById";
27
28 SqlParameter paramCustId = new SqlParameter();
29 paramCustId.ParameterName = "@CustId";
30 paramCustId.SqlDbType = SqlDbType.NChar;
31 paramCustId.Direction = ParameterDirection.Input;
32 paramCustId.Value = "ALFKI";
33
34 SqlParameter paramCustomerName = new SqlParameter();
35 paramCustomerName.ParameterName = "@CustomerName";
36 paramCustomerName.SqlDbType = SqlDbType.NVarChar;
37 paramCustomerName.Direction = ParameterDirection.Output;
38
39 cmd.Parameters.Add(paramReturn);
40 cmd.Parameters.Add(paramCustId);
41 cmd.Parameters.Add(paramCustomerName);
42
43 conn.Open();
44 SqlDataReader reader = cmd.ExecuteReader();
45
46 string custName = cmd.Parameters["@CustomerName"].Value.ToString();
  

View 6 Replies View Related

Retrieving Data From Stored Procedure

May 25, 2008

Hi,

For a particular application i retrieve records using stored procedure and bind the same to the data grid and display the same. The data are fetched from a table say A joined with couple of tables to staisfy the requirement. Now this table A has around 3700000 + records. The Select statement is as

Select column1, column 2, column 3...................... column 25
from table A

inner join hash table D
on A.Column3 = D.Column3
and A.Column4 = D.Column4
and a.nColumn1 = @ParameterVariable
Inner join table B
on a.nColumn1 =b.nColumn1
inner join table C
on A.column2 = C.column2


Indices defined on A are
IDX1 : Column 3, Column 4
IDX2 : Column 1

Around 350 records per second are inserted into the table A during the peak operation time. During this time when executing the procedure, it takes around 4 mins to fetch just 25000 records.

I need to fine tune the procedure. Please suggest me on same.

Thanks


View 4 Replies View Related

ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure

Mar 12, 2008

Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlDbType

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")

Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)

testCMD.CommandType = CommandType.StoredProcedure

Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)

RetValue.Direction = ParameterDirection.ReturnValue

Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)

auIDIN.Direction = ParameterDirection.Input

Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)

NumTitles.Direction = ParameterDirection.Output

auIDIN.Value = "213-46-8915"

PubsConn.Open()

Dim myReader As SqlDataReader = testCMD.ExecuteReader()

Console.WriteLine("Book Titles for this Author:")

Do While myReader.Read

Console.WriteLine("{0}", myReader.GetString(2))

Loop

myReader.Close()

Console.WriteLine("Return Value: " & (RetValue.Value))

Console.WriteLine("Number of Records: " & (NumTitles.Value))

End Sub

End Class

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.

Thanks in advance,
Scott Chang

View 29 Replies View Related

Retrieving Return Value From Stored Procedure Declaratively

Nov 1, 2007

Hi.
I have a stored procedure "sp1" which returns a value (with the sql statement Return @ReturnValue).
Is it possible for my asp.net page to retrieve this return value, and to do it declaratively (meaning without writing code to connect to the database in the code behind). If it is possible to do it like this please tell me how, and if not please tell me how to do it with code.
Thanks in advance .

View 5 Replies View Related

Getting Error When Retrieving Data From Stored Procedure

Jan 9, 2008

Hi,
I'm at my wit's end with this. I have a simple stored procedure:
PROCEDURE [dbo].[PayWeb_getUserProfile] ( @user_id_str varchar(36), @f_name varchar(50) OUTPUT)AS
BEGINDECLARE @user_id uniqueidentifier;
SELECT @user_id = CONVERT(uniqueidentifier, @user_id_str);
select @f_name=f_name FROM dbo.tbl_user_profile WHERE user_id = @user_id;
ENDRETURN
The proc runs perfectly from the Management Console. In my ASP.net form (C#), the following always results in: "Invalid attempt to read when no data is present." (I'll highlight the line where the error results):
SqlConnection conn = new SqlConnection(<connection string data>);SqlDataReader reader;
SqlCommand comm = new SqlCommand("dbo.PayWeb_getUserProfile", conn);comm.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter f_name = new SqlParameter("@f_name", SqlDbType.VarChar, 50);
f_name.Direction = ParameterDirection.Output;comm.Parameters.Add(f_name);
comm.Parameters.Add("@user_id_str", SqlDbType.VarChar, 36).Value = Membership.GetUser().ProviderUserKey.ToString().ToUpper();reader = comm.ExecuteReader();Response.Write("Num: " + reader["@f_name"].ToString()); // ERROR: Invalid attempt to read when no data is present.
reader.Close();conn.Close();
The data is there. When I put a watch on 'Reader', I see the correct first_name data there.
Your help is appreciated.

View 8 Replies View Related

Retrieving Output Parameter From A Stored Procedure

Feb 28, 2008

Lets say I have the following stored procedure

Code:

View 2 Replies View Related

Stored Procedure Retrieving Duplicate Data

Apr 17, 2008

Hi i have the following stored procedure which should retrieve data, the problem is that when the user enters a name into the textbox and chooses an option from the dropdownlist it brings back duplicate data and data which should be appearing because the user has entered the exact name they are looking for into the textbox. For instance
Pmillio Jones
Pmillio Jones
Pmillio Jones
Robert Walsh
Here is my stored procedure;
ALTER PROCEDURE [dbo].[stream_UserFind]
-- Add the parameters for the stored procedure here
@userName varchar(100),
@subCategoryID INT,@regionID INT
AS
SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategoriesON
Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHEREuserName LIKE COALESCE(@userName, userName) OR
SubCategories.subCategoryID = COALESCE(@subCategoryID,SubCategories.subCategoryID);

View 11 Replies View Related

Retrieving Parameter List For A Stored Procedure

Jul 23, 2004

Is there a way to retrieve the parameter list for a given stored procedure?

I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.

Thanks,
Allen K.

View 1 Replies View Related

Retrieving Server Time Using Stored Procedure

Jul 21, 2000

Hello all,

We have a domain where all computers are on GMT(Greenwitch Mean Time). We have an access front end that timestamps certain fields according to the client time that the program is running on, but we will be moving our client workstations off of GMT time and keep our SQL Server on GMT time, and want to keep the timestamps GMT.

So, I wanted to know if it was possible to create a stored procedure that gets the Server's time and returns it to the Access frontend for entry into the timestamp fields?

Or, if anyone has a better idea of how to get the time from the server to use on the clients, I would greatly appreciate it!!!

Kevin Kraus

View 2 Replies View Related

Problem With Stored Procedure And Retrieving Inserted Identity Value

Jul 11, 2006

Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne

View 3 Replies View Related

How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters

Oct 15, 2007



Hi,

I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.

My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?



Other question :

Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.


Thanks !

JF

View 7 Replies View Related

Default Stored Procedure

Jun 5, 2008

Hi,
I wanted to use a default for yearly instead of monthly for stored procedure, but couldn't figure out how. I usually used stored procedure @start and @end for date.
i.e. this is monthly by default
@start = jul 2007 and @end= aug 2007;
i.e. i want yearly by default
@start = jul 2007 and @end= jul 2008.

Is this too confusing?

Please help!!!!!!!!!

View 1 Replies View Related

Execute Stored Procedure From Stored Procedure With Parameters

May 16, 2008

Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY

CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC

Any assistance would be greatly appreciated.
Thank you,
Dave

View 6 Replies View Related

Default Value Of Stored Procedure Parameter

Jul 20, 2007

 Hi,This works:CREATE PROCEDURE MyProc    @Date smalldatetime = '2005-01-01'AS... But this does not CREATE PROCEDURE MyProc    @Date smalldatetime = GETDATE()AS... I'm talking about sql2005. Can anyone help how to overcome this? 

View 3 Replies View Related

Stored Procedure Default Value Question

May 14, 2004

Is there a way inside a stored procedure to determine if a parameter defined with a default value was explicity passed the same value as the default vs. the parameter was omitted and you just got the default value.

E.g. create procedure mysp_stuff @parm1 varchar(20)=NULL, @parm1 bit=0 as
if (@parm1 is null)
begin
-- did we get the default or did the caller pass NULL
end
if (@parm2 = 0
begin
-- did we get the default or did the caller pass 0
end
GO

EXEC mysp_stuff
go
EXEC mysp_stuff @parm1=NULL, @parm2=0
go

View 5 Replies View Related

Stored Procedure To Change Default

Dec 14, 2005

Can someone explain to me why the following doesn't work?declare @oname sysnameselect @oname=name from sysobjects where name like"df__mytable__mycol%"alter table mytable drop constraint @oname

View 4 Replies View Related

Default Value For A Stored Procedure And Conversion Error

May 30, 2008

When I call this function and the database field 'Login' is null, then I get an error message "Conversion from type 'DBNull' to type 'String' is not valid." I've supplied a default value in my stored procedure and I've also provided a default value in the result value in the function.  How do I get around this?  Thanks 
lblLastUserLogin.Text = GetLastUserLogin().ToString()
Private Function GetLastUserLogin() As String        Dim result As String = ""        Dim con As New SqlConnection("server=x.x.x.x;database=database;uid=x;password=x")        Dim cmd As New SqlCommand("GetLastUserLogin", con)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = lblUserID.Text        cmd.Parameters.Add("@ReturnVal", SqlDbType.SmallDateTime).Direction = ParameterDirection.Output        Using con            con.Open()            cmd.ExecuteNonQuery()            result = CType(cmd.Parameters("@ReturnVal").Value, String)        End Using        Return resultEnd Function 
CREATE PROCEDURE [dbo].[GetLastUserLogin]     -- OUTPUT parameter to hold the count.    @UserID int,     @ReturnVal smalldatetime = ' ' OUTPUT AS     -- This will return the last date returned by the SELECT query.     Set @ReturnVal = (SELECT MAX(Login) FROM TUserLogs WHERE UserID=@UserID)

View 2 Replies View Related

CANT We Set Default Value Null To Parameter In Stored Procedure

Feb 18, 2008



Hello

I've written a stored procedure with 4 parameters

create procedure dummy
( @a int, @b int, @c varchar(50), @d varchar(50))

now from front end(I'm using c#.net)
I want to send the values according to some criteria
So in the process...I've only values for @a & @c ...

so In order to reduce the code of sending Null values explicitly to other parameters...

can't I set like default values for it so that If I don't send values to certain parameters it will have the default value or Null value.

like I want something like this:

create procedure dummy
( @a int NULL, @b int NULL, @c varchar(50) NULL, @d varchar(50) NULL)

View 1 Replies View Related

Stored Procedure Using A Parameter Default To Select ALL Records - Help Please

Jan 6, 2006

Hi everyone,
I have created a stored procedure in sql server with parameters for my c# application. Wanted to know is there anyway to set the default value for @searchpostcode to select all the records?
Right now it brings the records based on the postcode specified .(I have dropdownlist in my c# application that passes the parameters for postcode)
My stored procedure:
CREATE PROCEDURE sp_accepting_practice  (@searchpostcode as nvarchar(100))  AS
SELECT   dbo.tbdentists.Title, dbo.tbdentists.FirstName, dbo.tbdentists.Surname, dbo.tbpractices.PracticeName, dbo.tbpractices.PracticeAddress1, dbo.tbpractices.PracticeAddress2, dbo.tbpractices.Town, dbo.tbpractices.Postcode, dbo.tbpractices.Phone, dbo.tbdentistspractices.ListNo, dbo.tbtreatment.treatmentNatureFROM         dbo.tbdentists INNER JOIN dbo.tbdentistspractices ON dbo.tbdentists.DentistId = dbo.tbdentistspractices.DentistId INNER JOIN                      dbo.tbpractices ON dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId AND                       dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId INNER JOIN                      dbo.tbtreatment ON dbo.tbdentistspractices.TreatmentId = dbo.tbtreatment.treatmentIdWHERE   dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'ORDER BY dbo.tbpractices.PracticeId
EXECUTE sp_accepting_practice   G4GO
I greatly appreciate your help. Thanks in Advance
Regards
Shini
 
 
 
 

View 9 Replies View Related

Stored Procedure Parameters

Oct 3, 2006

Hi,I have a problem with a call to stored procedure on SQL server. I created stored procedure for inserting a row to a table.In my application I read data from ASP.NET form and prepare parameters for calling my stored procedure. Like:            SqlCommand sc = new SqlCommand("AddToMyTable");            try            {                sc.CommandType = CommandType.StoredProcedure;                sc.Connection = new SqlConnection("myconnectionstringhere");                SqlParameter param;                param = sc.Parameters.Add("@MyFirstParam", SqlDbType.NVarChar,50);                param.Value = "something";                 ..... here I repeate "add" and value assignment for all my parameters            }...When I call ExecuteNonQuery(); I get exception that one of the parameters for stored procedure is expected but not supplied. However, I can find this very same parameter in my source code to be added to list of parameters. I think my problem is caused by large number of parameters. I have 55 parameters, half of them are of type nvarchar(50) and half of them of type bit. My reasoning is based on the fact that if I remove parameter that is reported to be missing from my stored procedure and from my application, I get the same error with next parameter in the list.Where should I increase maximum number of parameters that are allowed? Do I have some buffer problem? Where to specify that I need more space for parameters? Nenad 
 

View 10 Replies View Related

A Stored Procedure With Many Parameters

Jan 1, 2008

There is a form in my project which users can add their emails through it. The maximum numbers of emails are 60. I put a textbox and a button for adding email. But I don’t know which of the following solutions is suitable:
1.       After each click by user , the insert stored procedure will be called
2.       All the emails entered by users will be saved and then, the insert stored procedure will be called. This SP must have a loop but I am not sure about the right code for this SP because I have to define 60 input parameters for this SP.
 

View 3 Replies View Related







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