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.treatmentNature
FROM 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.treatmentId
WHERE dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'
ORDER BY dbo.tbpractices.PracticeId
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?
If a sqldatasource is programed to send textbox1.text to a stored procedure, and the .text property is left empty, and there is no default value set for the parameter, what exactly is the stored procedure receiving?I would like to run a IF BEGIN statement on the value of the parameter in the stored procedure but the following does not work:IF @Parameter IS NULL BEGINor IF @Parameter = '' BEGINThe only way I've gotten it to work is if I set the default value of the parameter being sent to a specific alphanumeric value. Then do something like:IF @Parameter = '99' BEGIN<Code Here>END
I will be calling a stored procedure in SQL Server from SSIS. The stored procedure inserts records in a table by accepting input parameters. In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure.
How can I call this stored procedure in SSIS? This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.
I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
I have a report with a dataset/parameter to select the salesperson.
SELECT DISTINCT [DatabaseName$Sales Shipment Header].[Salesperson Code], [DatabaseName$Salesperson_Purchaser].Name, 1 AS SortID FROM [DatabaseName$Salesperson_Purchaser] RIGHT OUTER JOIN [DatabaseName$Sales Shipment Header] ON [DatabaseName$Salesperson_Purchaser].Code = [DatabaseName$Sales Shipment Header].[Salesperson Code] UNION ALL SELECT NULL AS [Salesperson Code], 'Alle salespersons' AS Name, 0 AS SortID ORDER BY SortID, [DatabaseName$Salesperson_Purchaser].Name
In the preview mode the "All salespersons" is selected. When I deploy I see in the web user interface "<Select a Value>". The second choice is "All salespersons".
Is there a way to see "All salespersons" in the web user interface? How do I select a default value from my dataset of salespersons?
In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).
I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?
[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]
Hi, I have report parameter and its values are static lik (ABC, DEF, GHI ) etc. I want to select all as a default for this parameter. How I can do this?
SELECT ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM UserArticlesWHERE (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate) When I use the above on a GridView I get nothing. Using SQL manager it works great. I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do. I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date. Thanks for any help!
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= ''
AS BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end
Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
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 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
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
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
Has anyone encountered this before? Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied. It seems that my code is not passing the parameter to the stored procedure. When I click this hyperlink: <asp:HyperLink ID="HyperLink1" Runat="server" NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>' Text='<%# Eval("Name") %>' ToolTip='<%# Eval("Description") %>' CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'> </asp:HyperLink> it is suppose to get the country name and description, based on the country id. I am passing the country id like this. protected void Page_Load(object sender, EventArgs e) { PopulateControls(); } private void PopulateControls() { string countryId = Request.QueryString["CountryID"]; if (countryId != null) { CountryDetails cd = DivisionAccess.GetCountryDetails(countryId); divisionNameLabel.Text = cd.Name; divisionDescriptionLabel.Text = cd.Description; } } To my app code like this: public struct CountryDetails { public string Name; public string Description; } public static class DivisionAccess { static DivisionAccess() public static DataTable GetCountry() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountry"; return GenericDataAccess.ExecuteSelectCommand(comm); } public static CountryDetails GetCountryDetails(string cId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountryDetails"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@CountryID"; param.Value = 2; param.DbType = DbType.Int32; comm.Parameters.Add(param); DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); CountryDetails details = new CountryDetails(); if (table.Rows.Count > 0) { details.Name = table.Rows[0]["Name"].ToString(); details.Description = table.Rows[0]["Description"].ToString(); } return details; }
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied Someone please help!
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)
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?
I have this update procedure that updates and encrypts a credit card column. I added the pass phrase to a table and now I'm trying to use that pass phrase in my update procedure. When I run the update from the app the error is that the procedure is not getting the @Phrase parameter. Can anyone see why this might be?ALTER PROCEDURE [dbo].[UpdatePayment]
i need to run a stored procedure from an asp page that will import data into a temporary table.
i am having trouble with passing the parameter from the Exec command (currently developing in QA). It works ok where i want to use a value such as 'PC', or 'printer' or 'laptop' where the import sql has a where clause of '... = <<parameter>>...'.
I want however to be able to pass it a value such as ['printer','laptop','pc']so that the procedure will collect the data into the temp table with an '...in <<parameter>>'
this falls over and retrieves zero rows. the parameter is passed to the sp correctly as i have used a print command to display it, it comes back as 'pc',printer','laptop'. It appears to be the ' in ' that is not parsing the parameter.
I have a problem when i want to construct a stored procedure. I want to pass a parameter with customerid's. The problem is that I don´t know how many customerId's I send to the stored procedure, because the user select the customerId's from a selectbox. The SQL string I want to excute looks like this
SELECT CustomerName FROM Customer WHERE CustomerId IN (199, 301, 408... )
How should I create the stored procedure so I put the customerId values in a parameter, like the procedure below? (I know that this not will work)
CREATE PROCEDURE rpt_PM2000_test(@SCustomerId varChar) AS BEGIN SELECT Customer FROM Customer WHERE CustomerId IN (@sCustomerId) END
In the Create portion of a stored procedure, does the line in bold mean that if the parameter @RegoDate is not provided, then use the current datetime?
