I'm trying to replace a view with stored procedure for faster performance. the View is called by end user using a query as below, I need to pass the date as parameter for sp to execute with quotes for it to execute with correct results. Â I tried to pass the date as parameter but could not execute stored procedure with correct results. Is there any way to put quotes around returned date from sub query :Â
Execute statement is like Exec dbo.storedProc1Â select max(date) from table
I need to pass the above as :
Exec dbo.storedProc '2015-03-24'Â .
Somehow passing the date as parameter is giving me empty result set.
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!!
Hi, this is my first post here, i'm working on a view wich I have to display the results by period. 1st period - Current month 2nd period - Last month 3rd period - Month before last and so on.
How can I achieve this? of course I have a column wich has the date I want to use, I was thinking on semthing like:
case when DATEDIFF(M, TRXDATE, getdate()) <= 0 then ammount END as Period1 (TRXDATE and ammount are columns on my table)
Guys Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number of periods betwen the startdate and enddate so for example
-- Initial data insert @Periods ( StartDate , Enddate , NumberOfMonthsInPeriod , TotPeriods-- for this illustration, initialised to 0 but need to be UPDATEd as per detail below )
select '30-Sep-2005', '10-Apr-2009', 1 -- 1 month period 0 union all select '30-Sep-2005', '10-Apr-2009', 3 -- 3 month period 0 union all select '30-Sep-2005', '10-Apr-2009', 6 -- 6 month period 0
The following rules regarding periods apply Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie 01-Jan - 31 Jan 01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...) and so on to December
Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year 01-Jan - 31-Mar 01-Apr - 30-Jun 01-Jul - 30-Sep 01-Oct - 31-Dec
Each 6 (NumberOfMonthsInPeriod) month period is one of the following 01-Jan - 30-Jun 01-Jul - 31-Dec
I need to derive TotPeriods as follows:
For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44
For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16
For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8
Some lots may not have any transactions for some of the periods between the start and end dates but I need to report every period between the start and end period for each lot. I have a period table that I thought I could use but haven't come up with a way to get the results I'm after.
Question: How to determine if a date value was between one of the date periods that appear in multiple rows?
Background: We have a table of "license valid" periods, wherein each license can have one or more rows. Â (As you know, a driver's license can be started, expired, renewed, suspended, reinstated, revoked, etc.) Â Instead of of having a license activity table--from which valid license periods could be extrapolated--we store just the periods for which a license was valid.
My task is to take a list of licenses and specific dates and determine if each license was valid as of that date, returning either true or false. Â What is the best way to accomplish this?
DECLARE @ValidityInQuestion TABLE ( LicenseID int , DateValidityInQuestion date);
DECLARE @LicenseValidPeriods TABLE ( LicenseID int , BeginDate date , EndDate date);
[Code] ...
 How then do I query both tables in order to get the same result that results from the following query?
SELECT 12345 AS LicenseID , '2015-01-15' AS DateValidityInQuestion , 1 AS LicenseActive UNION SELECT 67890 , '2015-02-04' , 0;
I assume I need to join on the LicenseID columns, but my understanding stops there.
I need to do the following and am hoping someone can help me out. I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to thestored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the datepassed into the stored proc needs to be compared to todays date. Todays date must be determined inthe SQL. So basically here is my pseudo code for what I am trying to accomplish. Basically I just am afterthe comparison of the two values: If @BeginDate < TodaysDate The difficult part is how to obtain the value for "TodaysDate" Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in. Can someone please code this out for me in Microsoft SQL. I would be forever grateful.
SELECT SUM(x.qty * ISNULL(p.price_mn,0)) FROM (SELECT [Code] = A.A.value('@code','varchar(20)'), [Qty] = A.A.value('@quantity','INT') FROM @xml.nodes('/DocumentElement/data/item') AS A(A)) X LEFT JOIN productprice_t P ON p.code_id = x.code
The question is, how can I do this if the XML is in a different format & doesn't use any attributes? So it looks like this (which I'm getting from an ADO.Net datatable using .WriteXML): <DocumentElement> <data> <code>ABCDEFG</mercurycode> <quantity>1</quantity> <sort>0</sort> </data> <data> <mercurycode>XCDFEG</mercurycode> <quantity>2</quantity> <sort>0</sort> </data> <data> <code>ABCDEFG</mercurycode> <quantity>10</quantity> <sort>0</sort> </data> </DocumentElement>
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.
I have a requirement where, I need to get the value of parameter from a query string of url and use it in my report. Ex:- http://www.mysite.com?name=bobby
From the above url, I have to take the value of name and be able to use it in my report query or assign to a parameter.
I have a boolean parameter on my report that the user will enter. Currently, it shows as an option button with True/False beside each option as the words used. Is it possible to have a check box instead of an option button for the user instead? Thanks in advance!
I am trying to pass multiple values as parameters into my update command:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@selectedusersparam)));I develop my parameter (@selectedusersparam) using the following subroutine: Private Sub btnAddUsersToDeployment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddUsersToDeployment.ClickDim iVal As Integer = 0Dim SelectedCollection As StringSelectedCollection = ""If (lsbUsersAvail.Items).Count > 1 ThenFor iVal = 0 To lsbUsersAvail.Items.Count - 1If lsbUsersAvail.Items(iVal).Selected = True ThenSelectedCollection = SelectedCollection & "," & lsbUsersAvail.Items(iVal).ValueEnd IfNextSelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))Session.Item("SelectedCollectionSession") = SelectedCollectionSqlDataSource4.Update()ltlUsersMessage.Text = String.Empty 'UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (@selectedusersparam))'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"ElseltlUsersMessage.Text = "Select users before adding to deployment. Hold Control for multiselect"End IfEnd SubFor some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the queryI don't understand why.
I am using two drop downs, like so: <asp:DropDownList ID="ChurchStateDrop" runat="server" DataSourceID="ChurchStateDropData" DataTextField="State" DataValueField="State" AutoPostBack="True" onselectedindexchanged="ChurchStateDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All States" /></asp:DropDownList> <asp:DropDownList ID="ChurchCityDrop" runat="server" DataSourceID="ChurchCityDropData" DataTextField="City" DataValueField="City" AutoPostBack="True" onselectedindexchanged="ChurchCityDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All Cities" /></asp:DropDownList> I have ????? in the value fields because I don't know what value needs to be passed to negate filtering (to choose all). The Dropdowns have the following SQLDatasources:<asp:SqlDataSource ID="ChurchStateDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [State] FROM [ChurchView]" DataSourceMode="DataReader"></asp:SqlDataSource> <asp:SqlDataSource ID="ChurchCityDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [City] FROM [ChurchView] WHERE ([State] = @State)" DataSourceMode="DataReader"> <SelectParameters> <asp:ControlParameter ControlID="ChurchStateDrop" Name="State" PropertyName="SelectedValue" Type="String" /> </SelectParameters></asp:SqlDataSource> Now, lets say I wanted to pass a value to the WHERE statement in ChurchCityDropData to coincide with 'All States', what would I replace value="??????" with? Now you may think I'm crazy to do such a thing, but this actually has to do with adding a Denomination Dropdown to show Denominations from all states or all cities. I will figure out the best logic for that later, I just want to know the wildcard to pass to the parameter to choose all states (negate filtering).
I am new to sql and very familiar with access. I am using a verylarge database(130M records) in ms sql2000 and think I need to frontend it with access for reports and forms, etc..I have some questions:1) Is there a way in SQL to prompt a user for input at the running ofa query like the [what is your name] construct in an access query?2) I can't seem to create calculated fields in a view in sql. Inaccess I put "total:=hours*rate" and a new column would be createdwhich would contain the total.3) I normally would create a form in access from which the accessqueries would pull their variables and then insert those variables inthe queries or reports, etc. How would this be accomplished usingsql.4) Lastly and probably most basic. How does a "real" sql developercreate reports and forms to interact with the enduser? I am usingaccess as a front end, but am open to suggestions.Thanks,Brad
I have the follwoing stored procedure:ALTER procedure [dbo].[up_GetExecutionContext](@ExecutionGUID int = null) asbeginset nocount ondeclare@s varchar(500)declare @i intset @s = ''select @s = @s + EventType + ','-- Dynamically build the list ofeventsfrom(select distinct top 100 percent [event] as EventTypefrom dbo.PackageStepwhere (@ExecutionGUID is null or PackageStep.packagerunid =@ExecutionGUID)order by 1) as xset @i = len(@s)select case @iwhen 500 then left(@s, @i - 3) + '...'-- If string is too long thenterminate with '...'else left(@s, @i - 1) -- else just remove the final commaend as 'Context'set nocount offend --procedureGOWhen I run this and pass in a value of NULL, things work fine. When Ipass in an actual value (i.e. 15198), I get the following message:Invalid length parameter passed to the SUBSTRING function.There is no SUBSTRING being used anywhere in the query and thedatatypes look okay to me.Any suggestions would be greatly appreciated.Thanks!!
I'd think this is possible but I'm having trouble getting data returned from the query. Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table. This will be inside a SP. If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU.
Can't I just build the WHERE by replacing @SearchField with its value ? I've looked up the CASE statement but I don't think it does what I need.
I have a SP that inserts data into a table with an identity column I want to get that id and use it in my response.redirect however I keep getting 0 back instead of the identity. Relevent Sql 2005 Declare @NEWEMPID int --after an insert happens in sqlSet @NEWEMPID= SCOPE_IDENTITY()--last thing in my sqlReturn @NEWEMPID Or I tried NEWEMPID as an output parameter VB.Net Dim retValParam As New SqlParameter("@NEWEMPID", SqlDbType.Int) retValParam.Direction = ParameterDirection.ReturnValue'execute the sqldatasource insert command sqlEmpNewEmp.Insert() Dim EmpIDVar As Integer = Convert.ToInt32(retValParam.Value) Response.Redirect("http://www.mysite.com?EMPID=" + Server.HtmlEncode(EmpIDVar)) sqlEmpNewEmp.InsertParameters.Clear()
I have a requirement to show the parent value which is passed and also all the leaf member details in MDX. For example in Adventure works, if I pass Bikes as the value in MDX, the results should display Bikes along with All the child member names as a measure value in SSAS MDX. I only have one hierarchy with Product Category-> Subcategory-> Product.  I am trying to use the following MDX but I am getting all the members. I need to get only Bikes in my report.  WITH MEMBER [Prod_Passed] AS SETTOSTR(AXIS(1)) MEMBER [Prod_Child] AS DESCENDANTS ([Dim Product].[Hierarchy].CURRENTMEMBER,,LEAVES).item(0).NAME MEMBER [Child_Value] AS ([Dim Product].[Hierarchy].CURRENTMEMBER,[Measures].[Sales Amount]) SELECT [Dim Product].[Hierarchy].[Product Category].&[1] * [Dim Product].[Product Key].[Product Key].MEMBERS ON ROWS, {[Measures].[Sales Amount],[Prod_Passed],[Prod_Child],[Child_Value]} ON COLUMNS FROM [AdvWorks]
I want to update a record that has a datetime field in it. But what if there is no parameter given for that field, i.e. what is the correct type/value to pass as parameter?
Example: A DOB field for a user profile, but the user doesn't enter his birthday
This is some example code that I use to update:
Private Sub UpdateDOB(ByVal dob As Date)
Dim parameters As SqlParameter() = { _ New SqlParameter("@Birthday", SqlDbType.DateTime, 8)}
parameters(0).Value = dob
'Run Stored Procedure
This gives me the exception "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM" when I don't pass a valid date. I tried passing Nothing but same error (in this case it convert dob to "#12:00:00 AM#")
The only thing I can think of is to use Date.MaxValue and then check in application logic, but there must be a better way!
select substring(ISNULL(CAST(FullAdress AS NVARCHAR(MAX)),''),1,charindex(',',ISNULL(CAST(FullAdress AS NVARCHAR(MAX)),''))-1) from tbl_lrf_company_details_with_codes
but i am getting the error as "Invalid length parameter passed to the SUBSTRING function." Please advice Thanks In advance
I am not sure about the architecture of the Issue Tracker and hence not sure if it applies here. But I will post in any case and wait for users on this forums comments as well.
===========Earlier post================== This question is regarding the architecture of TimeEntry. In some programs it builds an arrayList for Master-detail type of relationship and when user is ready to save it by clicking 'submit' it build a variable with pipe delimited fields. This is then passed to a sql query.
This to me does not seem to be an efficient manner. Because the max character is 1500 chars as parameter to SQL query.
I was wondering if instead I could store it as an XML and then use the XML to import in to SQL.
Any ideas is greatly appreciated, I am running in to problems where my variable construct does increase to more than 1500 chars. Any thoughts are much appreciated in this regards.
Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?
I am using a simple procedure to pivot results (found in another forum and adapted it). It is done on SQL Server 2005 server with all service packs. Procedure: ************** ALTER Procedure [dbo].[EthnicityPivot] @StDate as Datetime, @EndDate as Datetime as begin DECLARE @Teams varchar(2000)
truncate table ForEthnicPivot
INSERT INTO ForEthnicPivot SELECT DISTINCT COUNT(ID), Team, Ethnicity FROM dbo._EthnicityByTeamEpisode where Startdate between @StDate and @EndDate GROUP BY Ethnicity, Team
SET @Teams = '' --// Get a list of the pivot columns that are important to you.
SELECT @Teams = @Teams + '[' + Team + '],' FROM (SELECT Distinct Team FROM ForEthnicPivot) Team --// Remove the trailing comma
SET @Teams = LEFT(@Teams, LEN(@Teams)-1) --// Now execute the Select with the PIVOT and dynamically add the list --// of dates for the columns EXEC( 'SELECT * FROM ForEthnicPivot PIVOT (SUM(countID) FOR Team IN (' + @Teams + ')) AS X' ) end ************
I can call the function: exec EthnicityPivot '01/01/2007','09/09/2007'
and it works fine in SQL analyzer, but when I want to use it in Visual Studio in a new report I am getting this error message:
There is an error in the query. Invalid length parameter passed to the SUBSTRING function. Incorrect syntax near ')'.
I have a procedure that calls a SVF to convert an xmldocument. The ultimate purpose is to update the xml in a column in a multi-million row table. The xml is stored as varchar(MAX), it was supposed to carry any type of text, initially at least.
My question is: why is the xml-parsing performed inside the function much slower when i pass the xmldocument as type xml than when it is passed as varchar(MAX) and the CAST to xml is within the function? Does processing the xml input parameter in SlowFunction involve expensive crossing of some context border?
The two versions of the SVF (they return the rowcount in this simplified example):
CREATE FUNCTION [dbo].[FastFunction] ( @inDetaljerText varchar(MAX) ) RETURNS int
[Code] ....
The two versions of the SP
CREATE PROCEDURE [dbo].[FastProcedure] AS BEGIN SET NOCOUNT ON; select dbo.FastFunction(al.Detaljer)
Hi, I am new at sql 2000 and 2005, I have created a package in 2005 which I am trying to execute on a daily bases by creating a job. At first because of security issues the job would not execute. Hence, I had to create a credential and a proxy to run the job with sa account. Now it is giving me this error, €œSQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. €œ Through research I have no clue as what I need to do, or where to look. The package runs without error when I execute the package itself. Any help is greatly appreciated. Thanks, Lori
