sql-server,sql-server-2008 , Concatenated string as parameter argument value does not give output in stored procedure
Concatenated string as parameter argument value does not give output in stored procedure
I am using SQL SERVER 2008 management studio.
I have a stored procedure which accepts several parameters.
One parameter name is
@recvemail with default value as
If the value of
'Y' then all the users who opted for email news letter should be displayed in the output.
'N' then users who do not wish to receive email letter should be included in the output.
If the value is
'B' then all the users should be displayed in the output, whether they who opted for news letter or not opted for news letter.
Now for default value
'B' i tried the set the parameter argument values as
'N' but I am not getting any output.
My stored proc is
Create procedure [dbo].[proc_getMembershipEmailbackup]
-- exec proc_getMembershipEmail @From=N'1991-06-11',@To=N'2017-06-14',@DevoteeName=N'',@MembershipName=N'Membership Life',@log_cond=N'',@recvemail=N'N'
@Email varchar(50)= '',
@Phoneno varchar(50)= '',
@MinAmount decimal =Null,
@MaxAmount decimal =NULL,
if @recvemail = 'B'
Set @recvemail='Y' + ',' + 'N'
if @MembershipName = 'ALL'
ROW_NUMBER() over (order by mu.f_name) as [No],
mu.f_name +' '+mu.l_name as DevoteeName ,
mu.pers_email as Email,
mu.home_phone as PhoneNo,
mp.name as MembershipName,
mu.recv_email as optedforemail,
d1.amt_due as amount_due,
CONVERT(varchar(10),d1.nextduedate,101) as duedate,
from d_user_memb d1
left join m_user mu on d1.user_id = mu.user_id
left join m_memb_pledge mp on d1.memb_id = mp.memb_id
d1.status in ('A','D','C')
AND(Isnull(@From,'') = '' or CONVERT(date,d1.nextduedate ) >= CONVERT(date,@From) )
and (Isnull(@To,'') = '' or CONVERT(date,d1.nextduedate ) <= CONVERT(date,@To ))
and (Isnull(@DevoteeName,'') = '' or (f_name +' '+l_name) like '%' + @DevoteeName + '%')
and (recv_email in (@recvemail ) )
and (Isnull(@MembershipName,'') = '' or (mp.name) = @MembershipName)
and (ISNULL (@Email, '') = '' or mu.pers_email = @Email)
and (ISNULL (@Phoneno, '') = '' or mu.home_phone = @Phoneno)
--and (ISNULL (@MinAmount,0) = 0 or cast(d1.amt_due as decimal(10,2)) >[email protected])
--and (ISNULL (@MaxAmount, 0) =0 or cast(d1.amt_due as decimal(10,2)) <= @MaxAmount)
@log_cond = 'b'
AND d1.amt_due BETWEEN @MinAmount AND @MaxAmount
@log_cond <> 'b'
AND @log_cond <> '0'
OR (@log_cond = '>' AND d1.amt_due > @MinAmount)
OR (@log_cond = '<' AND d1.amt_due < @MinAmount)
OR (@log_cond = '>=' AND d1.amt_due >= @MinAmount)
OR (@log_cond = '<=' AND d1.amt_due <= @MinAmount)
OR (@log_cond='=' AND d1.amt_due [email protected])
I tried to execute the procedure with values like this but I am not getting any output
exec proc_getMembershipEmailbackup @From=N'1800-06-11',@To=N'2099-06-14',@DevoteeName=N'',@log_cond='', @recvemail='B'
If the change the parameter value
'Y', I am getting output correctly and all users who opted for email letter is displayed correctly in the output.
Similarly I changed the value from
'N' I am getting the output correctly and all users who have not opted for email letter is displayed in the output.
Only for value
'B' I am not getting output. I should get the output such that all users who have either opted or not opted for email letter should be present in the output.
As Mark Sinkinson wrote in his comment, the
IN() operator expects a comma separated list of arguments, but you have supplied it with a single argument that contains a comma separated list of values.
Change this part of the stored procedure:
and (recv_email in (@recvemail ) )
and (recv_email = @recvemail OR @recvemail = 'B')
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...
I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have...
I have the following MSSQL query I am trying to convert to LINQ. I am using entity framework with the following syntax to get at the data. var rv = (from i in DC.TableA select i).ToList(); This is the sql I want to write a C# LINQ query for but...
I know we can simply create a new table and copy the old table by doing select * into tbl2 from tbl1 i would like to check if table tbl2 exists, if it does then copy all the rows, if it doesn't then create a new one without having to...
Background: I have a directory with a number of files that are imported to SQL server. Task: Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query. Ultimate objective: To display SQL results besides the filenames but the resultset...
Let' say I have two tables: table 1: col a | col b | col c 1 | 62215 | 21 1 | 62015 | 22 2 | 62215 | 23 2 | 51315 | 24 and table 2: col a | col b| col f 1 | 62015| z...
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...
I am receiving an error as below: PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE: [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Connection string is not valid . ' My codes are as follow: $this->link = new PDO( "sqlsrv:server=$this->serverName:$this->port;Database=$this->db", "$this->uid", "$this->pwd" ); I wish someone can enlighten...
I know looping is not ideal in SQL, but I couldn't think of another way of doing this. I want each distinct row from this Table 1 to have each distinct date and hour produced on Table 2. In other words, Table 2 has the dates between 05/01/2014 through 04/30/2015,...
I would like to use window authentication in my program to connect to my sql server. users already have certain permissions on the SQL server and I would like to leverage that in my program. The way I currently connect to the server is using this connection string. Dim ConnectionString...
As described in the title, I am using SQL Server 2012 Parallel Data Warehouse with Polybase feature to try to access a HDInisght Hadoop cluster. As a starting point for every connection to Hadoop from SQL Server, I find to execute the command sp_configure @configname = 'hadoop connectivity', @configvalue =...
As the title says I cannot browse and cannot see backup folder. is there any other way to restore bak file ? or how can I fix this ? ...
I have a TestPack table that can have n no of Lines associated with it. But a Line may exist without a TestPack. Later on, a Line might be assigned a TestPack#. What this relationship is called and how do I implement this relationship in SQL Server 2008?...
I am creating a SQL Query dynamically. After it's been created I want to execute it and store it as a temporary table. WITH [VALIDACCOUNTS] AS( EXEC (@sqlQuery)) ...
I am trying to retrieve data from one table and then insert it into another table. This is a a sample of the first table in which there is the following data. tb1 is the table which consists of data. The two columns Manager and TeamLeader basically means for example...
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...
I have a connected SQL Server database in Visual Studio and am displaying its content in a grid. I created a dropdown menu with the column names as selectable options and a text field to filter for specific content, e.g., DropDown = "Start" - Textfield = 14.03.2015 = Filter Column...
I have this problem, I am a newbie in SQL. I have 3 tables: TBL_PRODUCT: prod_id, prod_type, prod_code, prod_desc TBL_DOCUMENT: doc_id, doc_name, doc_expiry, doc_term, enable_amt TBL_DOCUMENT_GROUP: grp_id, prod_type, doc_type, doc_id For every document, there are corresponding products, one to many. What I can't figure out is how can I display...
I am trying to write a procedure that inserts calculated table data into another table. The problem I have is that I need each row's calculated column to be influenced by the result of the previous row's calculated column. I tried to lag the calculation itself but this does not...
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...
Trying to return 0 if any of these columns have no values returned, in my particular case 'Past Due' has no values to total, so it is returned, so i get the return in the second snippet here. How can I return something if these are no values to count?...
I've 2 Dropdownlist called States and Country. but only the States DDL(DropdownList) will be enable the country DDL will be diable. when I select a option in States which is already stored in SQL. The Country DDL Should Show the Selected state's Country automatically from DB. Please help Me..... Ex:If...
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...
One of my sql table is injected with some html code. It is inserted such that the html tags are inserted after actual data. How to remove this from my table.
I have a business in my Date. I need change date below for each date before 25 of each month I want get 25 of month before and for each date after 25 of each month I want get 25 of the same month. Example : for 2015/06/24 (or 2015/06/06)...
I have two tables, one with various details such as Username, email id, user code and another table with columns which are: UserCode, supervisor email id, active_flag I required an output of a table which only displays the active users i.e. the users with active_flag (Y or N) column as...
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...
Emp_No Emp_Shift Emp_Date 500 AL 1/5/2015 600 S 2/5/2015 600 H 3/5/2015 500 S 4/5/2015 500 AL 5/5/2015 600 AL 6/5/2015 I need help on this issue , HOW TO RETURN count in 3 Columns >> EX: Emp_No Count Al Count S Count H 500 2 1 0 600 1...
I have this situation in a stored Procedure: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE...
I want a query to make which return 1 when the time is in between of 10:00 pm - 5:00 pm. Else it should return 0. Select * from table ...
I need to update table using order by clause in sql server 2008 e.g Update tblTempChek Set TmpCheckIn='15:50:03' Where TempID IN ( Select TempID From tblTempChek Where convert(date, TmpDate)='2015-06-23' AND UserID='1' Order By TempID Desc ) but this gives error Msg 1033, Level 15, State 1, Line 3 The ORDER...
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...
My table looks like this: CREATE TABLE MyTable ( TableID INT IDENTITY NOT NULL, ForeignID INT NOT NULL, Value sql_variant NOT NULL, CodeOne VARCHAR(4) NOT NULL, CodeTwo VARCHAR(4) NOT NULL ) I'm trying to do a insert with the following code: INSERT INTO MyTable(ForeignID, Value, CodeOne, CodeTwo) VALUES ( 1,...
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...
I have this two columns. One is filled with some data, second is null. col1|col2 --------- null| 72 null| 72 null| 72 null| 33 null| 33 null| 12 null| 12 null| 55 null| 72 I want to generate values for col1 that will gather and group values from col2. So...
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...
Can anyone help me find the column or columns in this table where data would be truncated? I am trying to execute the SQL insert below, but it is failing with the error: String or binary data would be truncated. Which I understand means a value is too big to...
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...