LIKE Statement With A Parameter Value
Hi i'm trying to do this:
SELECT Name, Address1 FROM Stores WHERE Name LIKE %@SearchText%"
but it's not working. I assume my syntax is off?
Thanks!
View Complete Forum Thread with Replies
Related Forum Messages:
SQL Parameter And LIKE % % Statement
Hi there, I am trying to use and SQL statement with a LIKE query with % at the begining and end of the parameter to get any combination of the search parameter i.e. last name. My piece of SQL looks like this: WHERE userUsername=@Username AND contactName LIKE @SearchTerm I need something like this: WHERE userUsername=@Username AND contactName LIKE %@SearchTerm% However this does not work. Any suggestions would be greatly appreciaed. Regards, TCM_
View Replies !
In Statement With Parameter
Hi, I have a query that uses a parameter as criteria e.g. type = :_type (i set p_type = 'Trad') I want to change this parameter to be a range of values. However when I change the query to be type in (:_type) I get nothing returned if I set p_type to be 'Trad,New' but I do get something if it's just Trad. Can anybody advise on the syntax? I've tried a number of variations.
View Replies !
Help With Date Parameter And If/else Statement
Hi,I'm trying to run a select statement that takes includes an if/elseclause. I need to select the 'tran_date' between....if the current month is greater than 10 i.e. after OCT then thetran_date will be between '01-Oct' - plus current year or thetran_date is '01-Oct' plus previous year.and the current dateHere is my script so far:SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,b.description PROJECT_CODE, sysdateFROM actrans a, acactivity b, dual cWHERE a.resource_type = 'E'AND a.acct_category = 'TIME'and a.activity like '9-WW-357852%'and b.activity = a.activityand tran_date betweenif to_char(sysdate, 'MM') > 10)begina.tran_date = '01-Oct' & to_char(sysdate, 'YY')endelsebegina.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))EndORDER BY tran_dateCan anyone help me out with this? I need to run it as a job, but notsure whether I should be using a stored procedure etc..Thanks in advance.George
View Replies !
Passing Parameter To Sql Statement
I am trying to pass a parameter to an sql statement that executes a stored proc. Here is my sql statement from the Execute SQL Task component. USE [master] GO DECLARE @return_value int EXEC @return_value = [dbo].[sp_sendSMTPmail] @To = N'john.r.carter@bankofamerica.com', @Subject = ?, @Body = NULL, @Importance = NULL, @Cc = NULL, @Bcc = NULL, @Attachments = NULL, @HTMLFormat = NULL, @From = N'bpm@bankofamerica.com' SELECT 'Return Value' = @return_value GO I have the ByPassPrepare set to True. Can anyone tell me why I can't get this to work. Thanks, JC
View Replies !
Passing Parameter To IN Statement
Hi, I try to pass a parameter to a IN statement in SSRS but couldn't get it work. It likes following sample: select * from T1 where name IN (@nameList) I passed @nameList like 'aa', 'bb', ''aa'', ''bb''(two single quotations), all didn't work. I couldn't even get it work on "Reporting -> Data" screen. What is the right way to pass a parameter to IN statement in SSRS? I am using C# and SSRS2005. Thanks!
View Replies !
Multi Parameter AND Statement
Is it possible for an AND statement to take mulitple parameters? See below code: SELECT tblQuestion.Question, tblAnswer.Answer FROM (tblAnswer INNER JOIN tblQuestion ON tblAnswer.QuestionID = tblQuestion.ID) WHERE (tblAnswer.StateID = ?) AND (tblAnswer.QuestionID = 14) Is something like this possible?: AND (tblAnswer.QuestionID = 14, 26) Thanks
View Replies !
Remove Parameter In A Select Statement
I have a select statement that I am using and wanted to know if there is a way to remove a parameter on the fly. What I want to do is remove the @status if the text = "". so it will only search by the date and endate is that possible to do. Here is my code.
View Replies !
Parsing A SQL Statement As A Parameter And Executing It
Hi all,I was just wondering if it could be possible to excecute a statementwhich is extracted from variabele, Example:declare @SqlStatement varchar (100)set @SqlStatement = 'select * from company'the @SqlStatement contains the actual sql query, is it possible somehow to straight away convert it into excecution?I've tried:exec @SqlStatementbut exec or execute are meant to be for proceduresAny idea?
View Replies !
Pass Column Name Using Parameter In SQL Statement...
Hi,I am trying to Pass Column Name(FieldName) using Parameter in SQLStatement... But i am getting error...how can i pass Column name using parameter???Example:in table i have fieldname ECountry...Select @FName='ECountry'Select @FName from Table...How it works???Thanx in Advance,Regards,Raghu...
View Replies !
Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code' Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"' but I can't seem to come up with the right statement. Any help greatly appreciated. Thanks, Judith
View Replies !
Openrowset With Parameter In Exec Statement?
Hi! Is it possible to call proc with parameter in openrowset?: declare @str varchar(100); select @str = 'EXEC SP_HELPTEXT ''createReport'''; select * from OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', @str) AS a; Incorrect syntax near '@str'. ------------------ select * from OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', 'EXEC SP_HELPTEXT ''createReport''') AS a; Msg 7357, Level 16, State 2, Line 1 Cannot process the object "EXEC SP_HELPTEXT 'createReport'". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object. Best regards B. D. Jensen
View Replies !
Can I Create A 'Top N' Statement Within A Stored Procedure Using A Parameter?
In a 'Top n' type statement I wish to be able to insert the n valuefrom a parameter, within a stored precedure egHaving declared @pageSize as a parameter I want to run the followingtype of query :SELECT DISTINCT TOP @pageSize routeID, routeName FROMtblRoute_HeaderWhen I attempt to do so I get an error mesage indicating incorrectsyntax. I do not get an error message if I specify 'n' directly as inTOP 10Am I missing something or is this not possible within a storedprocedure?Best wishes, John Morgan
View Replies !
Case Statement On Where Clause If Parameter =NULL
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right: SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr FROM dbo.Engr_BOM_Control bom WITH (nolock) INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock) ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID CASE WHEN @v_Bill_Type IS NOT NULL THEN AND bom.Bill_Type=@v_Bill_Type END
View Replies !
Prepared Statement ......... Expects Parameter @CORP_NAME, Which Was Not Supplied
Prepared statement '(@CORP_NAME varchar(150),@REP_NAME varchar(150),@REP_TC_NO varch' expects parameter @CORP_NAME, which was not supplied I know this is a classical error and I searched through the forum but I could no solve it. I am sure that I defined @CORP_NAME, but it says you did not. My code is below,please help me... private void Submit1_ServerClick(object sender, System.EventArgs e) { erkaner = Page.Session.Contents["CORP_ID"].ToString(); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox32.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox27.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TC_NO", System.Data.SqlDbType.VarChar, 50, TextBox28.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ID", System.Data.SqlDbType.Int, 4, erkaner));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_EMAIL", System.Data.SqlDbType.VarChar, 50, TextBox29.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TEL", System.Data.SqlDbType.VarChar, 50, TextBox30.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_DUTY", System.Data.SqlDbType.VarChar, 150, TextBox31.Text)); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FOUND_YEAR", System.Data.SqlDbType.VarChar, 50, TextBox33.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@COUNTRY", System.Data.SqlDbType.VarChar, 50, DropDownListUlke.SelectedValue.ToString())); if (DropDownListUlke.SelectedIndex == 148) sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, DropdownlistSehir.SelectedValue.ToString()));else if(DropDownListUlke.SelectedIndex != 0 && DropDownListUlke.SelectedIndex != 148) sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, TextBox1.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SECTOR", System.Data.SqlDbType.VarChar, 150, RadioButtonList6.SelectedValue.ToString())); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMP_NUM", System.Data.SqlDbType.VarChar, 50, RadioButtonList1.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ACT", System.Data.SqlDbType.VarChar, 150, DropDownList3.SelectedValue.ToString())); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PC_NUM", System.Data.SqlDbType.VarChar, 50, TextBox35.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_USER_NUM", System.Data.SqlDbType.VarChar, 50, "INT_USER_NUM")); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_LAN", System.Data.SqlDbType.VarChar, 50, RadioButtonList3.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_SERVER", System.Data.SqlDbType.VarChar, 50, RadioButtonList4.SelectedValue.ToString())); sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ECOM_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox37.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@OT_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox38.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_CON_TYPE", System.Data.SqlDbType.VarChar, 50, RadioButtonList5.SelectedValue.ToString())); if (CheckBox1.Checked) sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, "Ticari Uygulamalar (B2B,B2C,e-iş,e-ihracat,…)"));else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox2.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, "Yönetişim Uygulamaları (Muhasebe, stok, satış, kalite, raporlama,denetim...)")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox3.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, "ERP Uygulamalari(Unity, SAP, JDE, BAAN, QAD vb.)")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox4.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, "Müşteri İlişkileri Yönetimi (CRM) Uygulamaları")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox5.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, "Bilgisayar Destekli Tasarım / Bilgisayar Destekli Üretim (CAD/CAM) Uygulamaları")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox6.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, "Veri Ambarı ve Veri Madenciliği Uygulamaları ")); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, "")); if (CheckBox7.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, TextBox36.Text)); else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, "")); sqlUpdateCommand1.CommandText = "UPDATE dbo.CORP_TBL SET REP_NAME = @REP_NAME, CORP_NAME = @CORP_NAME, REP_TC_NO =" + " @REP_TC_NO, REP_EMAIL = @REP_EMAIL, REP_TEL = @REP_TEL, REP_DUTY = @REP_DUTY, F" + "OUND_YEAR = @FOUND_YEAR, COUNTRY = @COUNTRY, CITY = @CITY, SECTOR = @SECTOR, EMP" + "_NUM = @EMP_NUM, CORP_ACT = @CORP_ACT, PC_NUM = @PC_NUM, INT_USER_NUM = @INT_USE" + "R_NUM, HAVE_LAN = @HAVE_LAN, HAVE_SERVER = @HAVE_SERVER, ECOM_SER_NUM = @ECOM_SE" + "R_NUM, OT_SER_NUM = @OT_SER_NUM, INT_CON_TYPE = @INT_CON_TYPE, APPLICATION1 = @A" + "PPLICATION1, APPLICATION2 = @APPLICATION2, APPLICATION3 = @APPLICATION3, APPLICA" + "TION4 = @APPLICATION4, APPLICATION5 = @APPLICATION5, APPLICATION6 = @APPLICATION" + "6, APPLICATION7 = @APPLICATION7 WHERE CORP_ID = @CORP_ID"; sqlUpdateCommand1.Connection = sqlConnection1; sqlUpdateCommand1.Connection.Open(); sqlUpdateCommand1.ExecuteNonQuery(); //This line gives the errorstring mySqlQuery = "UPDATE CORP_TBL SET FLAG = 1 WHERE CORP_ID=" + Page.Session.Contents["CORP_ID"].ToString();SqlCommand myCommand = new SqlCommand(mySqlQuery, sqlConnection1); myCommand.ExecuteNonQuery(); Page.Response.Redirect("main.aspx"); }
View Replies !
How Do I Assign A String To A Parameter I'm Passing To A Select Statement?
Hello, I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product. I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here: http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so: WHERE (OrderStatus IN ((select value from dbo.fn_Split(@StatusList,','))) OR @StatusList IS NULL) but now I don't know how to assign the string value to the parameter, say to '@solution_id'.my current select statement which was generated by Visual Studio 2005 looks like this: SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @solution_ID) ...but this only pulls results for the first item checked in the checkboxlist.Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.
View Replies !
Unable To Pass Queue Name In A Parameter For The FROM Clause Of RECEIVE Statement
I'm writing some generalized stored procedures for use form managed code. The following stored proc works great: CREATE PROCEDURE fnd_Send ( @Message VARCHAR(5000), @Contract SYSNAME, @MessageType SYSNAME, @FromService SYSNAME, @ToService VARCHAR(100), @Encrypted Bit ) AS BEGIN DECLARE @Handle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @Handle FROM SERVICE @FromService TO SERVICE @ToService ON CONTRACT @Contract WITH ENCRYPTION = OFF; SEND ON CONVERSATION @Handle MESSAGE TYPE @MessageType(@Message); Notice the use of SYSNAME parameters €“ translating the parameter into an object name - allows it to work in the BEGIN DIALOG statement when an system object is needed. Works create and cues up generic messages. The following will not compile: CREATE PROCEDURE fnd_Receive ( @Queue SYSNAME, @Message VARCHAR(5000) out, @MessageType SYSNAME out ) AS BEGIN DECLARE @Handle UNIQUEIDENTIFIER; RECEIVE TOP (1) @Handle = conversation_handle, @MessageType = message_type_name, @Message = message_body FROM @Queue; END GO It won€™t let me use the SYSNAME field @Queue in the FROM clause. I€™m getting an unfriendly: Msg 102, Level 15, State 1, Procedure fnd_Receive, Line 15 Incorrect syntax near '@Queue'. It looks like maybe I can€™t bind a variable in the FROM clause €“ perhaps like you cannot in a SELECT statement. I can fall back to dynamic SQL in the stored proc or in the managed code; I was hoping not to. Can anyone shed any light on this? Also, are there any other techniques I'm missing to avoid falling back to a dynamic SQL statement. Thank you in advance - Jeff Odell Catapult Systems
View Replies !
Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database. I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver? I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns. First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement? Second problem: What do I set the value of the parameter to? The code looks like: CallableStatement cstmt = myConnection.prepareCall(sQuery); cstmt.registerOutParameter(1, Types.OTHER); // What is the right type? cstmt.setNull(1, Types.OTHER); // What is the right type? if (cstmt.execute()) { ResultSet rs = cstmt.getResultSet(); } Execution results in a NullPointerException from the driver. What am I doing wrong? Thanks for your assistance. Jon Weaver
View Replies !
ERROR: Procedure Expects Parameter '@statement' Of Type 'ntext/nchar/nvarchar'.
/* INFO USED HERE WAS TAKEN FROM http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 */ DECLARE @X VARCHAR(10) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @Num_Members SMALLINT SELECT @X = 'x.dbo.v_NumberofMembers' DECLARE @SQLString AS VARCHAR(500) SET @SQLString = 'SELECT @Num_MembersOUT=Num_Members FROM @DB' SET @ParmDefinition = '@Num_MembersOUT SMALLINT OUTPUT' EXECUTE sp_executesql <-LINE 11 @SQLString, @ParmDefinition, @DB = @X, @Num_MembersOUT = @Num_Members OUTPUT Just Need Help On This Error Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. I dont know why im getting a errrror b/c I followed http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 exactly
View Replies !
Link List Data Region Parameter With Matrix Subreport Parameter
I have a report with a list data region and a GroupCd variable that pages after it changes. I'm trying to include a data matrix subreport on each page, where the GroupCd variable is used to determine what is included in the matrix. I'm using a multi-value parameter to select values of the GroupCd variable in the list. The parameter in the subreport must use the same parameter value as the one on the selected page in the list. So, my subreport (matrix) is set with single-value parameter. The parameters property in the subreport is defined as =Parameters!GroupCd.Value(0). When the report runs, the list data region pages by the selected GroupCd as expected. But, the subreport always renders data using the first value from the multi-value parameter from the list. Somehow, I need to link the indexed value from the array of parameter values listed on each page with the subreport. But I cannot figure out how to do this. The subreport parameter is not incrementing through the array of list values.
View Replies !
How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters
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 Replies !
Default Parameter Value Is No Longer Part Of Cascading Parameter In SSRS 2005?
Hi, I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly. I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not. Parameter 1 available values: from query default values: non query (specify a value "<None>") Parameter 2 available values: Non query (no value specified) default values: from query (based on Parameter 1) It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005. Is this a SSRS 2005 bug? is there any other work arounds or suggestions? Thanks. Kong
View Replies !
Stored Procedure With User!UserID As Parameter, As Report Parameter?
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
View Replies !
OLAP Datasource: Second Parameter List To Be Filtered Based On The First Parameter
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source? I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence. Can you do this thru SSRS? Any help would be great. Thanks for your time in advance. Kent
View Replies !
SSRS Toggle Parameter Between Multi-Value And Single Value Based On Another Parameter
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful. Thanks
View Replies !
Is It Possible To Dynamically Populate A Parameter List With Values Based On Another Parameter Value?
Is it possible to fill a parameter list with values based on another parameter value? Here's what I have so far (which hasn't worked)... I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school. I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table). I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school. Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up... The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'. The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid. ...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter? Any help you can give me would be great!! Thank you
View Replies !
Refreshing Parameter Values When A Higher Level Parameter Changes
Hi All. I have a parameter (hidden) that gets its value using an expression base on another parameter. When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes. When I change the parent parameter again, the value of the child parameter does not seem to change. How can I make this parameter change automatically when the parent is changed ? Any help will be appreciated. Thanks, Neil
View Replies !
Filters For Dataset From Parameter - How To Ignore If The Parameter Is NULL ?
Hi, I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset. The idea is the create snapshot nightly and then display the report filtered based on parameters to users. I'd like that the filter be ignored for a parameter if the parameter is passed as NULL, Right now I defined it this way : Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value) I do this for the 4 parameters that are passed, if they are null, make then equals to the field. I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute. Thanks
View Replies !
Drill To Report With Multivalue Parameter Without Defining The Parameter Value.
I have created a menu report listing available reports and am setting the Jump to settings of each text box in the list to take the user to the relevent report. But I am having a problem setting up drill down to reports with multivalued parameters. What I want to happen when the user drills down is for the report to open up with no parameter values selected. This works for single value parameters where I have set Null as the default value. But I have not been able to figure out how to do this with MVPs. Does anybody know if this is possible? As a workaround I have been using Jump to URL (rather than Jump to report), which has no problem opening the report without having to pass any values for the MPV. But I do not like using this as I use different DEV/UAT and PROD environments so would have to amend the URL for each environment. Seems like this should be possible... Any idea? Cheers, Mike PS: I should add I can Jump to reports with MVP without specifying a parameter in development studio but not when the report is published.
View Replies !
My Output Parameter Is Being Treated As An Input Parameter...why
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] ( @original_FormNum nvarchar(20), @NewRetVal INT OUTPUT ) AS SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int Set @stoptrans = 'NO' /* ---------------------- Search PART #1 ----------------------------------------------------- */ SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum ) SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum ) IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/ -----This means the delete can't happen........... BEGIN IF @AssignedFound > 0 AND @AvailFound = 0 BEGIN SET @NewRetVal = 1 END IF @AssignedFound > 0 AND @AvailFound > 0 BEGIN SET @NewRetVal = 2 END IF @AssignedFound = 0 AND @AvailFound > 0 BEGIN SET @NewRetVal = 3 END END ELSE BEGIN DELETE FROM dbo.Form WHERE dbo.Form.FormNum=@original_FormNum SET @NewRetVal = 0 ---Successful deletion END GO -------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
RS 2005: Stored Procedure With Parameter It Runs In The &&"Data&&" Tab But The Report Parameter Is Not Passed To It
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!! What is wrong? What I forgot?? Thankx for any help! Marina B.
View Replies !
Parameter: C, I, P, Or ALL...how To Select Based On Parameter?
Hi everyone, I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value... Any thoughts?? Query: sql Code: Original - sql Code ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @datesmalldatetime ) AS SELECT A1.uLastName, A1.uFirstName, A2.mName, A3.tStatus, A3.tScore, A3.tStartDate, A3.tCompleteDate, A4.cName FROM VSALCP.dbo.[User] as A1 INNER JOIN FSDLMS.dbo.Student as A5 ON A1.uID = A5.stID INNER JOIN FSDLMS.dbo.Transcript as A3 ON A3.t_FK_stID = A5.stID INNER JOIN VSALCP.dbo.Member as A2 ON A2.mID = A1.u_FK_mID INNER JOIN FSDLMS.dbo.Course as A4 ON A4.cID = A3.t_FK_cID Where @status = A3.tStatus ... ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @date smalldatetime )AS SELECT A1.uLastName, A1.uFirstName, A2.mName, A3.tStatus, A3.tScore, A3.tStartDate, A3.tCompleteDate, A4.cName FROM VSALCP.dbo.[User] AS A1 INNER JOIN FSDLMS.dbo.Student AS A5 ON A1.uID = A5.stID INNER JOIN FSDLMS.dbo.Transcript AS A3 ON A3.t_FK_stID = A5.stID INNER JOIN VSALCP.dbo.Member AS A2 ON A2.mID = A1.u_FK_mID INNER JOIN FSDLMS.dbo.Course AS A4 ON A4.cID = A3.t_FK_cID WHERE @status = A3.tStatus ... "If status = ALL select * status" Thanks for taking a look!
View Replies !
Get Parameter And Use In SSIS From SSRS Parameter
Hi, I am not sure this is the correct place to post this question, but here it is. I am trying to pass some parameters to SSIS from a report using the report parameter, then SSIS will create the datareaderdest and return to the report to use. Anyone have any idea, guidance or leads please share it out. Thanks in advance. Daren
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Compiler Is Not Recognizing My Using Statement For SglConnection Statement
I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code. Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message. Compilation Error Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error: Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 1 <asp:sqldatasource runat="server"></asp:sqldatasource> 2 <%@ Page Language="C#"%> 3 using System; 4 using System.Data; 5 using System.Collections; 6 using System.Data.SqlClient; 7 8 <script runat=server> 9 10 protected void Page_Load(object o, EventArgs e) 11 { 12 ObjectDataSource dsa; // This works no problems from the compiler here 13 SqlDataSource ds; // This works no problems from the compiler 14 15 if (IsPostBack) 16 { 17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text)) 18 { 19 instructions.Text = "Congratulations, your authenticated!"; 20 instructions.ForeColor = System.Drawing.Color.Red; 21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'"; 23 } 24 else 25 { 26 instructions.Text = "Please try again!"; 27 instructions.ForeColor = System.Drawing.Color.Red; 28 } 29 } 30 31 } 32 33 bool AuthenticateUser(string username, string password) 34 { 35 // Authentication code goes here 36 37 }
View Replies !
Case Statement Error In An Insert Statement
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it. Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB ); The error I'm getting is: Incorrect syntax near '='. I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View Replies !
Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement: SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Replies !
How To Show Records Using Sql Case Statement Or If Else Statement
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows as per data in MS Sql my tables are as follows 1)material fields are -- material_id,project_type,project_id,qty, -- 2)AB_Corporate_project fields are-- ab_crp_id,custname,contract_no,field_no 3)Other_project fields are -- other_proj_id,other_custname,po for ex : vales in table's are AB_Corporate_project ===================== ab_crp_id custname contract_no field_no 1 abc 234 66 2 xyz 33 20 Other_project ============ other_proj_id other_custname po 1 xxcx 111 2 dsd 222 material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7 i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project sample query i write :-- select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op but this query not work,also it gives errors i want sql query to show data as follows material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7 so plz help me how can i write sql query for to show the output plz send a sql query
View Replies !
The Formal Parameter &&"@ReportingId&&" Was Not Declared As An OUTPUT Parameter, But...what Is This?
After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ... An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.". I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc. The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows... Code Snippet select @ReportingId = max(ReportingId) from [dbo].[GuidToReportingid] where Guid = @UniqueIdentifier and EffectiveEndDate is null if @ReportingId is null BEGIN insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() END ELSE BEGIN select @rowcount = count(*) from [dbo].[someTable] where ReportingId = @ReportingId and...lots of other conditions ...later as part of an else it does the following... Code Snippet if @rowcount > 0 and @joinsMatch = 1 begin set @insertFlag = 0 end else begin update [dbo].[GuidToReportingId] set EffectiveEndDate = getdate() where ReportingId = @ReportingId insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() end ...and before the return it's value is inserted to different tables.
View Replies !
|
TRACKER
