Just check this sample , you have to use charindex(to find the index of comma) and substring function to get substring value Declare @var varchar(50) = ‘21699,21712’ select charindex( ‘,’, @var), substring ( @var, charindex(‘,’, @var)+1, len(@var)) you can also use this too: declare @var varchar(50) = ‘21699,21712’ select charindex( ‘,’, @var), substring ( @var, … Read more
Like this: Cast(create_date as date) as [Column Name Here] You can omit the [] if you are not using spaces or reserved words in your column name (which is good practice anyway). solved Give an Alias to a column I’ve casted as a date [closed]
Try this select id,[date],(case when refund = 1 then ‘R’ when voidstatus = 0 then ‘C’ when voidstatus = 1 then ‘V’ else ” end ) as [status],grossamt from trans 2 solved SQL Statement two columns value return single value [closed]
You need to store your counter in outside your method, like in SQL Server and then return the customer id. Below is a sample code: private static readonly object o = new object(); public static int GenerateNextCustomerId() { lock (o) { // Write code to get the next counter from db in this method var … Read more
Like here, here or here. SELECT [TicketId], STUFF(( SELECT ‘, ‘ + [Name]) FROM [OneTable] WHERE ([TicketId] = OT.[TicketId]) FOR XML PATH(”),TYPE).value(‘(./text())[1]’,’VARCHAR(MAX)’) ,1,2,”) AS Name FROM [OneTable] OT GROUP BY [TicketId] Go and vote it up, then close this question. 1 solved Getting Data separated by comma, [duplicate]
Try passing null value for unused parameters and inside the stored procedure put a check for nulls to switch tables. CREATE PROCEDURE [dbo].[proc_search_patient_ByID] ( @PatID_pk int , @Cntid smallint, @FamID int ) AS SET NOCOUNT ON IF @Cntid IS NULL BEGIN –Use select stmt of a table END ELSE –Use select stmt of another table … Read more
Since you have two columns that you now want to PIVOT, you’ll first have to unpivot those columns and then convert those values into the new columns. Starting in SQL Server 2005, you could use CROSS APPLY to unpivot the columns. The basic syntax will be similar to: select name, new_col, total from ( select … Read more
I usually use dynamic sql. Something like this create table #T ( ID int, aText1 varchar(4), aText2 varchar(4), aInt1 int, aInt2 int ) insert into #T select 1, ‘ABC1’, ‘XYZ1’, 2, 20 union select 2, ‘ABC1’, ‘XYZ2’, 3, 25 union select 3, ‘ABC2’, ‘XYZ2’, 1, 30 union select 4, ‘ABC2’, ‘XYZ1’, 4, 35 declare @sql … Read more
Not clear whether you want your result as a VARCHAR or an actual TIME type, so here’s both: DECLARE @Table TABLE ( TXN_TIME VARCHAR(6) ) INSERT INTO @Table SELECT ‘053124’ UNION SELECT ‘173932’ UNION SELECT ‘011815’ UNION SELECT ‘120349’ UNION SELECT ‘134207’ SELECT TXN_TIME, LEFT(TXN_TIME,2) + ‘:’ + SUBSTRING(TXN_TIME,3,2) + ‘:’ + SUBSTRING(TXN_TIME,5,2) + ‘:000000’ … Read more
If you are talking about the “Execute SQL Task”, expressions are possible. Here what I usually do is to configure the type as “direct input” and then define the whole statement – including my parametrized values – as expression. If you rightclick the task, you can select properties and in the properties you open the … Read more