I need to use a parameter in a stored procedure that is optional and if it is optional I need all records returned, even if there is an entry in the field that the parameter is appllied to. Is this possible? I know I can assign a default value. but the value in the field could be one of many choices. I want to be able to specify a choice and have only those records returned or leave it blank and return all records.
Thanks for any help,
Hello,
Is it possible to define optional parameters for a stored procedure?
What I want is to create a search with about 8 parameters, but most users will only use 3 or 4, so It would be nice If I could only provide the used parameters in my code. And have sql give the unused parameters a default value (possibly %)
thx.
Hi All,I have a stored proc which looks like this.Create ....(@id int,@ud int,@td int=0)if @td=0select bkah from asdf where id=@id and ud=@udelseselect bkah from asdf where id=@id and ud=@ud and td=@td---------------------------------I am wondering if i could replace if condition with the following lineselect bkah from asdf where id=@id and ud=@udand ( @td<>0 and td>@td )IS sql server 2000 smart enough not to use the td>@td in the query if@td is 0Thanks all
View 3 Replies View Related
hi all,
I am using SQL reporting services 2005, i like to have a parameter as optional, is there is any diresct way to set a parameter as optional without setting through the default value.
Hi, I'm having some issues with the SqlDataSource. I want to use it
to
populate a GridView, but using an optional parameter to filter the
results.
This is what I have right now (hopefully haven't made any typos
- can't
copy/paste):
<asp:SqlDataSource ID="test1" runat="server"
SelectCommand="SELECT * FROM
SomeTable WHERE (@MyParam IS NULL OR MyColumn =
@MyParam) ORDER BY
SomeColumn" ConnectionString="<%
ConnectionStrings:MyConnString %>" >
<SelectParameters>
<asp:ControlParameter Name="MyParam"
ControlID="DropDownList1"
PropertyName="SelectedValue" Type="String"
ConvertEmptyStringToNull="True"
DefaultValue=""
/>
</SelectParameters>
</asp:SqlDataSource>
<asp:DropDownList
ID="DropDownList1" runat="server" AutoPostBack="True">
<asp:ListItem
Selected="True" Value="">All</asp:ListItem>
<asp:ListItem>AnotherValue</asp:ListItem>
<asp:ListItem>SomeElse</asp:ListItem>
<asp:ListItem>Whatever</asp:ListItem>
</asp:DropDownList>
<asp:GridView
ID="GV" runat="server" DataSourceID="test1"
AutoGenerateColumns="False"
DataKeyNames="SomeID"
<columns>
<asp:BoundField
DataField="SomeColumn" HeaderText="A Title"
SortExpression="SomeColumn"
/>
(more bound columns here...)
</columns>
</asp:GridView>
When I test the SQL query in
the query designer it works (returns only rows
having the value passed as a
parameter when one is specified, otherwise it
returns all rows), so it seems
like that part is OK. The "All" (as in
"return all"/no filtering) entry in
the DropDownList has a value of a zero
lenght string, and the
ControlParameter has the convert empty string to null
to true (and the
default value is the same), so it should get converted to a
null when "All"
is selected, hence returning all rows. But it doesn't work.
It works fine for
all the entries with text, but the zero lenght string
somehow doesn't work -
I get no results at all instead of it returning all
rows (but the query
itself worked fine when I tested it).
What am I missing? I just can't
find what I'm doing wrong. Any ideas/hints?
(I also need to do the same with
an ObjectDataSource, so hopefully I can get
this to work!) I can't think of
an easy way to find out if the zero lenght
string gets converted to a null or
not (I've even tried adding OR @MyParam =
'' to the query and it still didn't
work....) Right now I'm stuck....
(Also posted this question on microsoft.public.dotnet.framework.adonet newsgroup)
Thanks a lot in advance for the
replies!
Carl B.
I want the procedure to check for the existence of a paramter and if it isthere, it will process these instructions, otherwise it will process theseinstructions. Any ideas? Thanks for your advice.Regards,CK
View 7 Replies View Related
Hello,
I want to use an optional parameter at the command line for my package.
I planned to make the first task a script which simply checks the variable (which is a string), and if it is empty, go one way, and if it is not, go another way. Is the best to go?
Many thnaks in advane
I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn€™t not pass in the parameter.
I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.
One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it€™s run! Not a good solution, but maybe it€™s the best I can do . . .
I have tried many different approaches using different functions, etc. but I€™ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:
1. To add the parameter to the WHERE clause and test for NULL I€™ve used the COALESCE function such as €œWHERE table.fieldname = COALESCE(@Param, table.fieldname)€?. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.
2. To handle multiple items, for example, if @Param = €˜3,7,98€™ (essentially, a csv separated list of keys)
Code SnippetWHERE table.fieldname IN(COALESCE(@Param, table.fieldname))
doesn€™t work because @Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I€™m using
Code SnippetISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
which works brilliantly in my WHERE statement AS LONG AS @Param ISN€™T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?
I€™ve tried
Code SnippetWHERE @Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was
Code SnippetISNULL (table.fieldname, 0) IN (CASE WHEN @Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@Param,€™,€™)))
This fails with €œSubquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression€? due to the multiple values in the parameter. (I can€™t understand why the line without the CASE statement works, but the CASE line doesn€™t!)
Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!
I have two tables: eg. a person-table (no nulls allowed), with an idand so on, and a person_course table (an intermediate table in amany-to many relationship between person table and courses tables),with two fields person_id and course_id.But I want to make ONE multipurpose stored procedure, which has ONLYoptional parameters on all fields in the person table AND the fieldcourse_id in the person_course table.I have no problems making optional parameters on the person table (eg.P.ID=ISNULL(@PersonID, P.ID ) ) BUT the problem is, when I try to addan optional parameter on the field course_id it dosn't produce theright results. Some times the course_id is null (because some personshavn't joined a class yet).Is there a way around it?Ole S. Pedersen
View 1 Replies View RelatedHello,
my problem is that i have a integer report parameter that must be multivalued. The parameter is populated by query. The thing is that in the beginning, there is no data in the dataset of the specific parameter. The table which is source to the dataset will br populated after some time from an XML.
Reporting services prompts the user to select a value for the parameter. But there is no value to select, yet. I cannot have leave blank because it is a string and not an int and i cannot have null because the parameter is multivalued. Any suggestions?
Thank you for your time and help!
/luskan
In my report I have two parameters PID and patientName, but user want to enter either PID or patientName. When I preview the report user can only enter one parameter not both. Is there any way  to create two parameter available but users can enter only one parameter value?Here is my report layout and query I used for that report. These two parameters comes from one column 'String_NVC' as from query and column name 'Description' as from the report layout.
SELECT Username_NVC AS Username,
String_NVC,
Time_DT AS UserActionDateandTime,
FROM test
[code]...
I have a ssrs report with Name, phone ,state and city as columns. I have check box as one of my parameter(optional). If user checks that checkbox then it should group by state, if checkbox is left blank no need to do any grouping. How can i do this in ssrs 2012.
View 3 Replies View RelatedThis code works EXACTLY as it should on a development server that has a Framework 1.1 hotfix (operating in the 'localhost' webserver environment) but not on a production server (in a live www. web app) that does NOT have the 1.1 hotfix. Is there a code problem (if so, why should it work in the localhost) or is the hotfix on the development server needed on the production server.
(in a button sub that does an update)...Dim currentUser As New AccBusiness.User( _ CType(Context.User, SitePrincipal)) Dim DDName As String = currentUser.DDName Dim NosData As String Dim AccountDetails As New SecureNums(txtDDPhone.Text, txtDDAddress.Text) NosData = AccountDetails.EncryptedData() AccountDetails.UpdateAcctDetails()----------------------(business object for the update)... Public Function UpdateAcctDetails() As Boolean Dim DataUserAcctNo As New Data.SecureCard(myModuleSettings.ConnectionString) Return DataUserAcctNo.UpdateAcctDetails( _ myDDName, _ myNosData) End Function--------------------------------(data object)...Public Function UpdateAcctDetails(ByVal DDName As String, _ ByVal NosData As String) As Boolean Dim rowsAffected As Integer Dim parameters As SqlParameter() = { _ New SqlParameter("@DDName", SqlDbType.VarChar, 50), _ New SqlParameter("@NosData", SqlDbType.VarChar, 512)} parameters(0).Value = DDName parameters(1).Value = NosData RunProcedure("sp_Accounts_UpdateAccount", parameters, rowsAffected) Return CBool(rowsAffected = 1) End Function------------------------------and the sproc:ALTER PROCEDURE sp_Accounts_UpdateAccount@DDNameVarChar(50),@NosDataVarChar(512)AsUPDATE Accounts_UsersSet NosData = @NosDataWHERE DDName = @DDName------------------------------------------------------------
It all works on the development server but on the production server it gives me an error :SProc sp_Accounts_UpdateAccount expects parameter "@DDName" which was not supplied.
Can anyone suggest why this works in one environment and not the other?
Thx for looking/Reid C.
I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.
Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.ALTER PROCEDURE dbo.ContactManagementAction
(
@ClientID int,
@Priority int,
@TStart datetime,
@TEnd datetime,
@Status nvarchar,
@ConTypeID int,
@Caller nvarchar,
@Keyword nvarchar
)
AS
SELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROM tblTasks
WHERE (ClientID = @ClientID) AND (Priority = @Priority) OR (Priority IS NULL) AND (ActionDate BETWEEN @TStart AND @TEnd) AND (Status = @Status) OR (Status IS NULL) AND
(ContactTypeID = @ConTypeID) OR (ContactTypeID IS NULL) AND (Caller = @Caller) OR (Caller IS NULL) AND (Subject LIKE @Keyword) OR (Subject IS NULL)
RETURN
Hi,
I've tried that but it does not work:
SELECT Field FROM Table WHERE Field LIKE @parameter
thanks
I am quite new to SSRS and am having some difficulties in trying to develop a new report via Business Intelligence Studio based on a stored procedure which requires the input of 1 parameter, and also has an optional parameter which I default to NULL in the sproc.
When I create my dataset I select the given sproc I want and when I attempt to execute it, I am prompted for the parameters the sproc expects.
However, when I enter a value in the dialog for the required Parameter I get a SQL error indicating that the parameter the sproc expects was not supplied. I have profiled the call and see the attempt to execute the sproc, but no parameter value.
Can some one tell me why the value I enter is not being passed to the sproc in my database ? Is there some special syntax that I need to use ?
I have scanned a number of sites & through the books I have and can't find anything on this. From what I have read, when I exec my sproc the parameters get recongnized & I can just enter my values. This doesn't seem to be the case.
Any help and/or suggestions are appreciated !!!
Thanks.
HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition
View 5 Replies View RelatedHello,
I am having trouble with a SPROC and I was wondering if anyone has had similar problems. I have a web form where users enter in a large amount of text, the web app saves that data via a SPROC into a varchar(8000) field in a SQL table. However, any amount of text beyond 255 gets cut off. I have double checked and tested my code and I dont seem to find the problem. Below is the beginning of my SPROC and as you can see, I have the correct data type and length. I have also verified the data type and length on the table and it is set at varchar(8000). Any ideas?
SPROC CODE
-----------------------------------------------
CREATE PROCEDURE Add_ProductsReview
(
@Pros varchar(100) = null,
@Cons varchar(100) = null,
@Title varchar(100),
@Body varchar(8000) -- This is the parameter with problems
)
AS......
Thanks!
In the below sproc I want to create a view based on the incoming parameter, which is a date passed on from VB in the format 01-01-2006.
Yet getting that to work, seems quite a challenge.
I've been dabbling a bit with the sproc and QA, passing the date parameter to it in varous forms, using both single, double and triple quotationmarks and changing the parameter type to datetime.
I either wind up with a syntax error " Incorrect syntax near '01' " or " Unclosed quotation mark before the character string ".
Anyone have a guess at how to fix this issue?
Cheers, Trinsan
REATE PROCEDURE KONTROL_DRdobbeltrapportering
@rapdato as varchar
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[KONTROL_vw_DRdobbeltrappbasis]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [KONTROL_vw_DRdobbeltrappbasis]
EXEC('
CREATE VIEW KONTROL_vw_DRdobbeltrappbasis
AS
SELECT Gramex_DW.dbo.Programhoved.[Rettet af DR],
Gramex_DW.dbo.Programlinie.Stationskode,
Gramex_DW.dbo.Programlinie.Udsendelsesdato,
Gramex_DW.dbo.Programlinie.[Udsendelses starttid],
Gramex_DW.dbo.Programlinie.Rapporteringsdato, Gramex_DW.dbo.Programlinie.Mediekode,
Gramex_DW.dbo.Programlinie.MUSANummer, Replace(Left(convert(varchar, [Gramex-ID]),11),''.'','''') AS GramexID,
Gramex_DW.dbo.Programlinie.Side, Gramex_DW.dbo.Programlinie.Tracknummer,
Gramex_DW.dbo.Programlinie.ISRC, Gramex_DW.dbo.Programlinie.Producentlandekode,
Gramex_DW.dbo.Programlinie.Spilletid, Gramex_DW.dbo.Programlinie.Minuttakst,
Gramex_DW.dbo.Programlinie.[Afregnet Station],
Gramex_DW.dbo.Programlinie.[Afregnet den],
Gramex_DW.dbo.Programlinie.[Afregnings ID], Gramex_DW.dbo.Programlinie.[Ur-opførelse],
Gramex_DW.dbo.Programlinie.Talestof, Gramex_DW.dbo.Programlinie.[Betalt af DR],
Gramex_DW.dbo.Programlinie.Kending, Gramex_DW.dbo.Programlinie.[Oprettelses ID],
Gramex_DW.dbo.Programlinie.[Oprettet den], Gramex_DW.dbo.Programlinie.[Oprettet af],
Gramex_DW.dbo.Programlinie.[Ændret den], Gramex_DW.dbo.Programlinie.[Ændret af],
Gramex_DW.dbo.Programlinie.[Eventuel delskæring], Gramex_DW.dbo.Programlinie.Plademærkenavn,
Gramex_DW.dbo.Programlinie.Katalogbetegnelse, Gramex_DW.dbo.Programlinie.Tracktitel,
Gramex_DW.dbo.Programlinie.Selskabsnummer, Gramex_DW.dbo.Programlinie.Indspilningsår,
Gramex_DW.dbo.Programlinie.Fonogramtitel, Gramex_DW.dbo.Programlinie.Plademærkenummer,
Gramex_DW.dbo.Programlinie.[Antal opførelser], Gramex_DW.dbo.Programlinie.[Rec nummer],
Gramex_DW.dbo.Programlinie.Indslagsnummer, Gramex_DW.dbo.Programlinie.[Afspillet gange],
Gramex_DW.dbo.Programlinie.[Afspillet minutter], Gramex_DW.dbo.Programlinie.Spilletidsløbenummer,
Gramex_DW.dbo.Programlinie.Pointtildelt, Gramex_DW.dbo.Programlinie.[Rettet af Gramex],
Gramex_DW.dbo.Programlinie.[Pgmkorlinie løbenummer], Gramex_DW.dbo.Programlinie.[Tape Kilde],
Gramex_DW.dbo.Programlinie.[Hoved artist], Gramex_DW.dbo.Programhoved.Produktionsnummer
FROM Gramex_DW.dbo.Programlinie
INNER JOIN Gramex_DW.dbo.Programhoved
ON (Gramex_DW.dbo.Programlinie.Stationskode = Gramex_DW.dbo.Programhoved.Stationskode)
AND (Gramex_DW.dbo.Programlinie.Udsendelsesdato = Gramex_DW.dbo.Programhoved.Udsendelsesdato)
AND (Gramex_DW.dbo.Programlinie.[Udsendelses starttid] = Gramex_DW.dbo.Programhoved.[Udesendelses starttid])
WHERE Gramex_DW.dbo.Programlinie.Rapporteringsdato >= ' + @rapdato + '
AND (Gramex_DW.dbo.Programlinie.Mediekode <> 1) AND
(Gramex_DW.dbo.Programlinie.Spilletid >= ''00:01:00'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2'') AND
(Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 ZULU'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 CHARLIE''')
GO
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data.
In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe.It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing).e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. I have been unable to replicate this using a test app and our test databases. Have observed selects failing to find even though the data is there, like this before?
I have an optional param passed to my sproc @Pid if that is populated i want to have it part of where clause, if it is null then not in where
I know this is wrong, and very new to sqlsrver:
WHERE (Pos = 'T') AND (ofDate= @OfDate)
IF not @PID is null
AND (PID = @PID )
GROUP BY
bla bla bla
Thanks
Could someone please tell me why the following SELECT statement...
SELECT ID, SpecimenNr, ScientificName, Locality, TaxonFROM petrander.QueryViewWHERE (InstitutionCode = 1) AND (Collectioncode = 1) AND (ScientificName LIKE N'%le%') AND (Locality LIKE N'%Fakse%') AND (22 IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...gives me 9 rows back, but embedding the exact same statement in the following sproc...
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = NULL, @museum int = NULL, @collection int = NULL, @binomen Nvarchar(254) = NULL, @locality Nvarchar(254) = NULLAS SELECT ID, SpecimenNr, ScientificName, Locality, Taxon FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE 'N%' + @binomen + '%' AND Locality LIKE 'N%' + @locality + '%' AND (@taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...and passing the exact same parameter values to with the following execute statement...
USE [Geomusdb]
GO
DECLARE @return_value int
EXEC @return_value = [petrander].[DynamicQuery]
@museum = 1,
@collection = 1,
@binomen = N'le',
@locality = N'Fakse'
SELECT 'Return Value' = @return_value
GO
gives me 0 rows!? What is different!?
Any help is greatly appreciated...
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
Hi, how can i make optional relationship?
for example: In table A, there is column 1, column 2, column3. In table B, there is column 4, column 5 and column 6.
column 1 and column 2 are primary keys for table A and table B. The relationships between table A and table B are column 2 and column 5; column3 and column 6. but optional (ie. when data exists in column 2, then column3 is null)
how can i set the relationship? because one of the columns data is null each time, error always occurs.
I have a select proc that will take a bunch or criteria parameters. Based on how many are not null I would like to decide how many inner joins to do.
for example:
select H1.Priceid as HotelPriceId,H2.Priceid as AirPriceId, H1.VendorPackageId from
(
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = 'SBT'
and Sitecode = 'system'
and PackageType = 'AHCF'
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = 'LAS'
and LengthOfStay = 5
and Ages = 'A2'
and ComponentType = @ComponentType_1
and ValidItemType = @ValidItemType_1
and ItemValue = @ItemValue_1
)
)
)H1 INNER JOIN
(
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = 'SBT'
and Sitecode = 'system'
and PackageType = 'AHCF'
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = 'LAS'
and LengthOfStay = 5
and Ages = 'A2'
and ComponentType = @ComponentType_2
and ValidItemType = @ValidItemType_2
and ItemValue = @ItemValue_2
)
)
)H2 on H1.Priceid = H2.priceId Inner Join
(
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = 'SBT'
and Sitecode = 'system'
and PackageType = 'AHCF'
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = 'LAS'
and LengthOfStay = 5
and Ages = 'A2'
and ComponentType = @ComponentType_3
and ValidItemType = @ValidItemType_3
and ItemValue = @ItemValue_3
)
)
)H3 on H2.Priceid = H3.priceId
if values are only passed in from @ComponentType_1,@ValidItemType_1,@ItemValue_1 I dont want to do any inner joins.
If its passed in for @ComponentType_1,@ValidItemType_1,@ItemValue_1 & @ComponentType_2,@ValidItemType_2,@ItemValue_2 I want to do the first Inner Join.
and of course if I get all 3 sets of criteria I want to do both the inner joins.
I know I can cut and past this thing 3 times with an if statement but that isn't going to be practical.
Is it possible to define an argument as optional for a UDF? I have a financial calculation that may or may not require a defined date range depending on the status of an individual item. Is there a way to avoid requiring the date range where it's not necessary?
View 10 Replies View RelatedI have a SP with many parameters. If not supplied, parameter default value is NULL.
The parameters are to be used in the WHERE clause:
ALTER PROCEDURE dbo.SearchFlight
(
@DepartureDate datetime = null,
@ReturnDate datetime= null,
@MaxPrice money= null,
@Country int= null,
)
AS
SELECT *
FROM myTables with joint
WHERE
DepartureDate = @DepartureDate
AND ReturnDate = @ReturnDate
AND MaxPrice = @MaxPrice
AND Country = @Country
If a parameter is null, I dont want it to be in the WHERE clause.
I dont want to use a string parameter and execute a "string" select.
I dont want to use a combination of IF to check everything ...
Is ther a solution ? using Case statement ???
Thanks for your help
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 RelatedHello everyone.
I have a package in DTS that I am tryinig to work with. We have a few queries that will export the same exact table to a text file. I am trying to make it so we can pull a previously created text file from a central "CommonFile" folder. In this package I have a Execute Package Task. This second package is only for creating this common txt file. I want this step to only run if the file is non-existant (i.e. someone deleted/moved it by accident) or if it is over a week old (the database we pull it from it updated on sundays). I can run it so the comment txt file get created first thing on monday, but I still want a failsafe in there incase the file is deleted/moved or the package to create it fails.
I have an ActiveX script set up which checks if the file exists and if it does then checks to see how old it is Then from there I would like to jump to a different area of my package:
Code Snippet
Dim pkg
Dim stpbegin
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(".txt") = TRUE THEN
msgbox("COPY")
Set objFile = objFSO.GetFile(".txt")
Dim today
Dim thisweek
today = date()
thisweek = (Weekday(date())-2)
IF thisweek < 0 THEN thisweek = 6 'Sunday Weekday() = 1 so this will put it at the end of the week.
IF (date() - thisweek) <= objFile.DateLastModified THEN
msgbox("COPY")
set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_4")
stpbegin.ExecutionStatus = DTSStepExecStat_Waiting
ELSE
msgbox("CREATE")
set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSExecutePackageTask_1")
stpbegin.ExecutionStatus = DTSStepExecStat_Waiting
END IF
ELSE
msgbox("CREATE")
set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSExecutePackageTask_1")
stpbegin.ExecutionStatus = DTSStepExecStat_Waiting
END IF
Main = DTSTaskExecResult_Success
End Function
The problem I am having is despite the ActiveX script the package will run the execute package task no matter what (i dont see a way to post pics so I will try to draw bellow). I think this is due to me having a success between the ActiveX and the EPT. This is the only way I know of how to step this up. If i do a completion it will also do the EPT and if I do a failure it won't do anything at all after it.
DTSActiveScriptTask_3 (from above) --> success --> DTSExecutePackageTask_1 --> success --> DTSActiveScriptTask_4
So basically I want this pagage to jump over the DTSExecutePackageTask_1 if the file checks out as okay. Does anyone have a suggestion of how I can set up this package?
I'm using an application that is generating some SQL scripts for SQL Server 2005. I'm trying tweak it so that I can run it on SQL Server 2000.
The line that I'm having trouble with is:
CREATE INDEX FKFFF41F9960601995 ON alf_access_control_entry (permission_id);(optional)
The key word "(optional)" is causing trouble.
I understand this keyword, when used in SQL Server 2005, let's the script continue and complete when errors are detected.
What is the alternative syntax to use in SQL Server 2000?
Thanks,
-Q
Hi, I'm having a problem trying to do optional items in where clause. I'd appreciate some help, heres the code.
select DFD.Col1 as 'Col1',CMD.Col2 as 'Col2'
from Control as DFD,MetaData as
CMD,Columns as DC,Tables as DT
where DFD.GroupID in
({0}) and
(CMD.MetaDataId = DFD.PrimaryID --
or CMD.MetaDataId = DFD.ForeignID -- These are the problem 3 lines
or CMD.MetaDataId=DFD.HumanColumn) --
and DFD.Disabled='N' and CMD.Disabled='N' and DC.ColumnsId=CMD.ColumnId
and DC.Disabled='N' and DT.TablesId=DC.TableID and DT.Disabled='N'
-- For the problem rows I need to be able to optionally search if they're = to MetaDataID. Right now this query will return only rows that have metadataid = PrimaryID or Foreign, Human. I need to be able to return all rows with the optional dependancies.