SQL Server 2012 :: Conditional Logic Function To Return VARCHAR Value With Gender
May 4, 2015
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function
GO Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[code]...
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.
My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms ( @enrollID INT , @CTCVersion VARCHAR(20) , @NumRecords INT ) RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13 Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
Hello.Looking for a smarter way to code the following. I have a storedprocedure I will be passing several variables to. Some times, some ofthe fields used in a WHERE clause will not be passed, and I would liketo avoid having to code a bunch of if statements to set the executingcode. For example, below I would only like to execute the LIKEconditions only when the variable in question is not NULL. I did atest and if the variable is set to null, obviously the select does notreturn what I'm expecting.if @switch = "B"SELECT * from ikb whereikbtitle like @ins1 andikbtitle like @ins2 andikbtitle not like @ins3 andikbbody like @ins1 andikbbody like @ins2 andikbbody not like @ins3endThanks for any help or information with this.
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session: DECLARE @PrVers NVARCHAR(128) , @PrVersNum DECIMAL(10,2) , @StageTable NVARCHAR(1024) = N''
The following T-SQL 2005 snippet executes a remote sproc on an Oracle database. DECLARE @OutParam VARCHAR(5) EXEC ('{Call getnextpin(1)}') AT ALTTEST GetNextPin takes a single input parameter. I need to modify the above code snippet to return the next PIN number into @OutParam. Any ideas? Any help appreciated.
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table set target_column = case when source_column_1= 'ABC'then 'XYZ' when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
I need to add a join in my select query depending upon a variable @LoggedUser. the Join is to be there if @loggedUser is 1 else i do not need it. Currently I am using two different queries one with join and one without it under If (@LoggedUser check).
the join is like - JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK) ON User_Count_Id = Analyst_Id_fk WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) Ana ON dep.Departure_Code = Ana.Primary_Analyst_Departure_Code_fk )
Any way that the join can be added conditionally in the query so i do not have to write the whole code again for one join.
I have two tables tabA (cola1, cola2, cola3) and tabB(colb1, colb2, colb3, colb4) which I need to join on all 3 columns of table A.
Of the 3 columns in tabA, few can be NULL, in that case I want to check the joining condition for the rest of the columns, so its conditional joining. Let me rephrase what I am trying to acheive - I need to check if the columns in joining condition is NULL in my 1st table (tabA), If so I need to check the joining condition for the rest of the two columns, if 2nd column is again NULL, I need to check the joining condition on the third column.
What I am trying to do is as below. Its working, but is very slow when one of the tables is huge. Can I optimize it or rewrite in a better way ?
--- First Create two tables Create table tabA (cola1 nvarchar(100), cola2 nvarchar(100), cola3 nvarchar(100)) Insert into tabA values (NULL,'A1','A2') Select * from tabA create table tabB
I have a function with multiple if ( condition) which is CPU intensive. How could I avoid this.
CREATE FUNCTION prici.[fn_pricipalamt] ( -- Add the parameters for the function here @Tcode char(10), @SecTypeCode1 char(10), @SecTypeCode2 char(10), @TradeAmount float,
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
I am new to T-SQL development and here's what I am trying to do.
declare @cmd varchar(255) set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)'
while (@cmd <=13) begin insert into #dbcheck values (@cmd) Set @cmd = @cmd + 1 end
but it is giving me an error
'Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)' to data type int.'
I'm getting syntax errors that just aren't helping me at all, so I thought maybe what I'm trying to do can't be done. I'm creating a UDF with 4 parameters, and I want it to return a result set (i.e. a table). But I want a different result set depending upon the value of one of the parameters. This works totally fine as a SP, but I can't tell where to put the RETURN clause(s) on the UDF. I've got: CREATE FUNCTION myFunction(@param1,...,@param4) RETURNS TABLE AS BEGIN IF @param1='x' BEGIN RETURN(SELECT columns FROM TableX) END ELSE BEGIN RETURN(SELECT columns FROM TableY) END END I get an "Incorrect syntax near the keyword 'IF'" error. I also tried using just one RETURN() wrapped around the outside of the IF construction (right after the very first BEGIN and before the last END), but to no avail. I get no errors when I run this logic as an SP. Is this type of construct not allowed in a UDF? Is there an alternative? I can't just leave this as a proc because I'm going to have to call these results from several views. Help!
When I get result from .NET console app, I get correct answer "JungleSektor". However, when SQL Server executes this code, it gives me "NT ServiceMSSQL $ SQL2014". How to get correct result?
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
Will both statements above access only a single page (as it fits into one page) or does the VARCHAR(MAX) always put its data on a separate page. If so, is the null insert treated differently from the '' insert?
CREATE PROC CLEAR_MY_TABLE @TableStat varchar(30) AS If OBJECT_ID('MyDB.dbo.' + @TableStat + '') is not null BEGIN ---PRINT 'I FOUND THE TABLE' DECLARE @count INT = -1; SELECT @count = (Select COUNT(*) FROM [dbo]. + @TableStat); IF (@count > 0) BEGIN TRUNCATE TABLE @TABLESTAT END
I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is 335:55:20.97 duration time. My code is below.
CPUTM = 335:55:20.97 duration time CPUZIPTM = 0:00:01.96 duration time
select * FROM [SMF_DATA].[dbo].[System_Management_Facility] WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) > '02:00:00.00')
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'TwoDigitsNumber' AND type = 'FN') DROP FUNCTION MinimumOFThree; GO CREATE FUNCTION TwoDigitsNumber(@a int) RETURNS nvarchar(20)
[Code] ....
The only first letter 'f', 's', 'e' is inserted in value instead 'first', 'second', 'equal'.
SELECT table1.col1 ,€™n/a€™ _response FROM table1 INNER JOIN table2
This is the query that get the report data for my report. Now I need to replace the second column with an actual response like €˜accepted€™ and €˜rejected€™. And these values should be a result of evaluation of this form
If(Statusarray.count < 1) Set _response = €˜accepted€™ Else Get Statusarray[1] Compare this to Statusarray[0] Set _response = some result based on comparision.
The _response returned in the query should return the actual response based on this evaluation where ResponseStoredProcedure is sent the current row value for table1.col1.
How can this be done in sql(using SQL Server 2005)
Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:
CREATE TABLE [dbo].[tblKppHierarchyPcl] ( [ID] NUMERIC(18,0) NOT NULL, [Name] VARCHAR(500), [PartStructureKey] NUMERIC(18,0) NOT NULL, [PartNumber] VARCHAR(500) NOT NULL, [ParentPartNumber] VARCHAR(500) NULL,
[code]...
Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.
declare @xmldoc as xml select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>' select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"
My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue AS ( declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt return @ConcatenatedString )
I know that if I have an nvarchar column I can use an equality like = N'supersqlstring' so it doesn't implicit cast as a varchar, like if I were to do ='supersqlstring'. And then I'll be a big SQL hero and all my stored procedures will run before a millisecond can whisper.
But if I'm comparing an nvarchar column to a varchar column, is it better to cast the varchar 'up' to an nvarchar or cast the nvarchar 'down' to a varchar?
For instance:
cast(a.varchar as nvarchar(100)) = an.nvarchar
or
cast(an.nvarchar as varchar(100)) = a.varchar
Leaving aside non-matching, like (at least I don't think) that SQL considers the varchar n to be equal to the nvarchar Å„, what's the best way to handle this?
Pretend for a moment that each column contains a mixed letter and number ID with no accented or wiggly-squiggly Unicode characters; it's just designs clashing.
Is there a performance hitch doing it one way or another? Should I use COLLATE? Should one of the columns be altered?
I have the following code in a SP. The 2 if statements for the @@ Error I added only for testing as I suddenly started get the following error at the 1st @@Error statement
Msg 232, Level 16, State 2, Line Arithmetic overflow error for type varchar, value = 10000.00000.
I cannot have this SP running minutes it must be done in seconds.
I have tried the folloiwng changes but then it takes to long.
