Menu
  • HOME
  • TAGS

Dynamically Creating Columns for an Unknown # of Columns

Tag: sql-server-2008,tsql,pivot,dynamic-sql

what I'm hoping to accomplish is to dynamically create columns as needed within a stored procedure. My issues lies in the fact that a drive may no incentives, 1 incentive or multiple (2,3,4.etc) incentives assigned to the drive. Screen shot below for example data:

Example Data

I initially used the FOR XML Path to concatenate records:

Stuff ((Select ', ' + isnull(EM.Description,'')
    From EquipmentMaster EM
        Inner Join EquipmentDetail ED On EM.EquipmentID = ED.EquipmentID And EM.EquipmentType=3
        Where ED.DriveID = DM.DriveID
            For XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1, 2, '') as [Incentives]

But the end users have requested the incentives be in separate columns. I am trying to reuse some code I found here: Stack Overflow link

But I cannot get it to work. Was hoping to get some help to make this procedure work.

Here is the procedure code:

declare
@startDate datetime,
@endDate datetime,
@RegID varchar(8000),
@OrgID varchar(8000),
@SubID varchar(8000),
@COID varchar(8000),
@RecID varchar(8000),
@StatusID varchar(8000),
@Cols nvarchar(max),
@Incentives nvarchar(max)

Select @Cols = Stuff ((Select ', ' + isnull(EM.Description,'')
            From EquipmentMaster EM
            Inner Join EquipmentDetail ED On EM.EquipmentID = ED.EquipmentID And EM.EquipmentType=3
            --Where ED.DriveID = DM.DriveID
            For XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1, 2, '')

Select
DM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],

Stuff ((Select ', ' + isnull(EM.Description,'')
    From EquipmentMaster EM
        Inner Join EquipmentDetail ED On EM.EquipmentID = ED.EquipmentID And EM.EquipmentType=3
        Where ED.DriveID = DM.DriveID
            For XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1, 2, '') as [Incentives]
From
rpt_DriveMaster DM
Left Outer Join rpt_Accounts Acct on DM.AccountID=Acct.AccountID
Left Outer Join IDViewRecruiters Rec on Acct.RecruiterID=Rec.CodeID
Inner Join rpt_CenterDetail CD on DM.CenterID=CD.CenterID

Where
DM.StatusID <>5
And DM.FromDateTime Between '03/01/2015' AND '03/02/2015'

Here is my original procedure that works to bring all incentives into one column (and used by SSRS to display a report):

Create Procedure Hema_IncentiveReport
@startDate datetime,
@endDate datetime,
@RegID varchar(8000),
@OrgID varchar(8000),
@SubID varchar(8000),
@COID varchar(8000),
@RecID varchar(8000),
@StatusID varchar(8000)

As

Begin

Select Item Into #Region From OBAPPS.[dbo].DelimitedSplit8K(@RegID,',');
Select Item Into #OrgCenter From OBAPPS.[dbo].DelimitedSplit8k(@OrgID,',');
Select Item Into #SubCenter From OBAPPS.[dbo].DelimitedSplit8k(@SubID,',');
Select Item Into #CollectionOp From OBAPPS.[dbo].DelimitedSplit8k(@COID,',');
Select Item Into #Recruiter From OBAPPS.[dbo].DelimitedSplit8k(@RecID,',');
Select Item Into #DriveStatus From OBAPPS.[dbo].DelimitedSplit8k(@StatusID,',');

Select
DM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
Case When DM.OpenToPublic = 1 Then 'Yes' Else 'No' End As [OpenToPublic],
Case When DM.OwnerType=0 Then 'Mobile' Else 'Fixed' End As [OwnerType],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
DT.EarlyShiftStart [EarlyShiftStart],
DT.LateShiftEnd [LateShiftEnd],
DrStat.StatusText [DriveStatus],
Acct.AccountID [AcctID],
Acct.InternalName [AcctInternalName],
Acct.ExternalID [ExternalID],
AD.Addr1 [Addr1],
AD.Addr2 [Addr2],
AD.City [City],
AD.State [State],
AD.Zip [Zip],
CO.CodeID [CO_ID],
CO.Description [CO_Desc],
Org.CodeID [Org_ID],
Org.Description [Org_Desc],
Rec.CodeID [Rec_ID],
Rec.Description [Rec_Desc],
Sub.CodeID [Sub_ID],
Sub.Description [Sub_Desc],
Reg.CodeID [Reg_ID],
Reg.Description [Reg_Desc],
DPaCT.ProcedureProjection [Proc_Proj],
DPaCT.ProductProjection [Prod_Proj],
DPaCT.ProceduresPerformed [Proc_Perf],
DPaCT.ProductsCollected [Prod_Coll],
DPaCT.QNS [QNS],
DPaCT.FTD [FTD],
(isnull(DPaCT.ProductsCollected,0))-(isnull(DPaCT.ProceduresPerformed,0)) [Alyx],

Stuff ((Select Distinct ', ' + isnull(MSM.ShortDesc,'')
    From Production.[dbo].rpt_DriveShiftDetail DSD
        Inner Join Production.[dbo].rpt_DriveShiftMobileDetail DSMD On DSMD.ShiftID = DSD.ShiftID
        Inner Join Production.[dbo].rpt_MobileSetupMaster MSM 
            On MSM.MobileType = 1 and MSM.MobileID = DSMD.MobileID
            Where DSD.DriveID = DM.DriveID
            For XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1, 2, '') as [Mobile_Short_Desc],

Stuff ((Select ', ' + isnull(EM.Description,'')
    From Production.[dbo].EquipmentMaster EM
        Inner Join Production.[dbo].EquipmentDetail ED On EM.EquipmentID = ED.EquipmentID And EM.EquipmentType=3
        Where ED.DriveID = DM.DriveID
            For XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1, 2, '') as [Incentives]

From
Production.[dbo].rpt_DriveMaster DM
Left Outer Join Production.[dbo].rpt_Accounts Acct on DM.AccountID=Acct.AccountID
Inner Join Production.[dbo].DriveTimes DT on DM.DriveID=DT.DriveID
Left Outer Join Production.[dbo].rpt_AddressDetail AD on DM.SiteID=AD.SiteID
Left Outer Join Production.[dbo].IDViewRecruiters Rec on Acct.RecruiterID=Rec.CodeID
Inner Join Production.[dbo].rpt_CenterDetail CD on DM.CenterID=CD.CenterID
Inner Join Production.[dbo].IDViewCollectionOp CO on CD.CenterID=CO.CodeID
Inner Join Production.[dbo].IDViewRegion Reg on CD.Region=Reg.CodeID
Inner Join Production.[dbo].IDViewOrgCenter Org on CD.OrgCenter=Org.CodeID
Inner Join Production.[dbo].IDViewOrgSubCenter Sub on CD.OrgSubCenter=Sub.CodeID
Inner Join Production.[dbo].rpt_DriveStatusDef DrStat on DM.StatusID=DrStat.StatusID
Inner Join Production.[dbo].DriveProjectionandCollectedTotals DPaCT on DM.DriveID=DPaCT.DriveID
Inner Join #Region As Region On Reg.CodeID = Region.Item
Inner Join #OrgCenter As OrgCenter on Org.CodeID = OrgCenter.Item
Inner Join #SubCenter As SubCenter on Sub.CodeID = SubCenter.Item
Inner Join #CollectionOp as CollectionOp on CO.CodeID = CollectionOp.Item
Left Outer Join #Recruiter as Recruiter on Rec.CodeID = Recruiter.Item
Inner Join #DriveStatus as DriveStatus on DrStat.StatusID = DriveStatus.Item

Where

DM.StatusID <>5
And DM.FromDateTime Between @startDate AND @endDate

Best How To :

I recreated the table as a matrix and created a row grouping on Drive ID field. And then I created a column group on the Incentive field and set the value to: =IIF(IsNothing(Fields!Incentive.Value),True,False)

Update Query With Inline

sql-server-2008,sql-update

Update d Set totalenteredtoday = te.IdCount FROM prodinformation As d JOIN ( Select [someJoinAttribute] ,Count(ID) As IdCount From @location Where entrytype IN ( Select validentrytype From vet With (Nolock) Where ltrim(rtrim(entrydate)) = @entrydate ) Group By [someJoinAttribute] ) As te On d.[someAttribute] = te.[someJoinAttribute] here [someJoinAttribute] would be the column/attribute...

SQL Server Stored procedure declaring variable in different way

sql-server,tsql,stored-procedures

@UPA nvarchar(30) = NULL is the procedure argument/parameter (in this case, an optional parameter though since it's being declared like that having NULL as the default value. So while calling procedure if you don't supply @UPA, it will assume NULL) that you need to supply while calling the procedure like...

SQL Server : copy data from columns to another table with many columns

sql,sql-server,sql-server-2008

As a best practice you should write the insert statement as: Insert into [PFTracking].[dbo].[UserInput](Col1,Col2,...) -- list the names of columns explicitly in which data is to be inserted SELECT [PTAID] ,[RequestDate] ,[ProvName] ,[Amount] ,[INorOUT] ,[Supplier] ,[Customer] ,[Program] ,[IssueDesc] ,[Chargeable] ,[Company] FROM [PFTracking].[dbo].[TempTable] You can omit the column names that get...

Convert row to columns SQL dynamically [duplicate]

sql,sql-server,sql-server-2008,tsql

First you will need to unpivot your table and after that pivot again. This is the key idea: select * from TableName unpivot(v for xyz in([Atest],[Btest],[Ctest]))u pivot(max(v) for yyyymmdd in([20150525],[20150526],[20150527]))p Fiddle http://sqlfiddle.com/#!3/a675c/1 As for dynamic sql, you can find many example of how you can use STUFF function to concatenate...

SQL compute AVG time, but the given time is in NVARCHAR format

sql-server-2008

are you looking for this ? Set Nocount On; If Object_Id('tempdb.dbo.#sometable') Is Not Null Begin Drop Table #sometable; End If Object_Id('tempdb.dbo.#computetable') Is Not Null Begin Drop Table #computetable; End Create Table #sometable ( RowId Int Identity(1,1) Primary Key ,TimeTaken Nvarchar(8) ) Create Table #computetable ( RowId Int Primary Key ,ComputeTime...

many rows into a single column with SQL

sql,sql-server,tsql

Here is what you can use: SELECT DISTINCT ParagraphID , STUFF(( SELECT N' | ' + CAST([LoginName] AS VARCHAR(255)) FROM [dbo].[CM_Signature] f2 WHERE f1.ParagraphID = f2.ParagraphID FOR XML PATH ('')), 1, 1, '') AS FileNameString FROM [dbo].[CM_Signature] f1 Note the STUFF("...", 1, 1, '') instead of STUFF("...", 1, 2, '')....

Looping distinct values from one table through another without a join

sql,sql-server,tsql,while-loop

So you want all distinct records from table1 paired with all records in table2? That is a cross join: select * from (select distinct * from table1) t1 cross join table2; Or do you want them related by date? Then inner-join: select * from (select distinct * from table1) t1...

Create a new column in my SQL View - Column must be two other columns combined

sql,sql-server,tsql,concatenation

You simply need to add another column to your select: CREATE VIEW KFF_Rep_Comm AS SELECT vw_KLX_RepCommTrx.AccName AS AccountName ,vw_KLX_RepCommTrx.Account AS AccountNum ,vw_KLX_RepCommTrx.RepID ,vw_KLX_RepCommTrx.RepName ,vw_KLX_RepCommTrx.CommPerc AS CommPercent ,vw_KLX_RepCommTrx.Credit ,vw_KLX_RepCommTrx.Debit ,vw_KLX_RepCommTrx.MUPercent AS MarkUpPercent ,vw_KLX_RepCommTrx.ProfitPercent AS GrossProfitPercent ,vw_KLX_RepCommTrx.SgdTrxAmt AS InvValue ,vw_KLX_RepCommTrx.SgdCost AS Cost ,vw_KLX_RepCommTrx.SgdProfit AS GrossProfit...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008

You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...

Force Update from a filtered DataGridView to Database

c#,sql-server-2008

Found out what was occurring, when I programmed the datagridview to show the searched data it did not change the bindingsource but left it the same from the Form Load (). this.log5TableAdapter.Fill(this.roboticlineDataSet.Log5); To resolve this I found the RoboticLineTable in the Dataset.xsd location in the Soultion Explorer. Here I selected...

SQL Subquery column equals operation

sql,sql-server,tsql

Try this query select u.ID, u.Name, case when p.value>0 then 'True' else '' end as IsPermissionEnabled from Users u left join permission p on p.UserID = u.ID and p.key='CanEdit' ...

One identifier for set of values

sql,sql-server,sql-server-2008,sql-server-2008-r2

You can solve this for example using the DENSE_RANK() function. See the example below: CREATE TABLE #yourTable(col1 int, col2 int) INSERT INTO #yourTable(col1,col2) VALUES(null, 72),(null, 72),(null, 72),(null, 33),(null, 33),(null, 12),(null, 12),(null, 55),(null, 72) SELECT * FROM #yourTable -- Your part: UPDATE yt SET col1 = numbering.number FROM #yourTable yt INNER...

Retrieve the Return Result with Chronological Order Based on Parameter

tsql,stored-procedures,sql-server-2012

Without an explicit ORDER BY Statement, SQL Server will determine the order using a variety of means e.g. collation\indexes\order of insert etc. This is arbitrary and will change over time! No Seatbelt - Expecting Order without ORDER BY If you want to guarantee the order of your output, you need...

Force date format regardless of culture

c#,sql-server-2008,currentuiculture

The answer is that if you are going to use any format then make sure its an ISO-type format such as yyyy-MM-dd. where END_DATE between '" + asOfDate.AddDays(-30).ToString("yyyy-MM-dd") + "' and However, the far more important point is use parameterised queries, not string concatenation. When you do this, a date...

Microsoft SQL Insert into subset of table columns fails [on hold]

sql-server,sql-server-2008

Check for constraints or triggers that would attempt to insert a value too large for a given column. This can happen when over time schema changes occur, and constraints or triggers escaped the scope of impact review. In this case (varchar(3)) column status had a default constraint that was attempting...

JPA annotation for MS SQL Server 2008 R2 IDENTITY column

hibernate,sql-server-2008,jpa,spring-data-jpa

I just found I missed setting up hibernate dialect on LocalContainerEntityManagerFactoryBean. After setting up org.hibernate.dialect.SQLServer2008Dialect as hibernate dialect, the GenerationType.IDENTITY works fine as Neil mentioned. Below is my spring data configuration and application.properties. FYI I have multiple datasource, one is H2 embedded datasource for internal use and the other is...

Get work time in minutes based on shift schedule

sql,sql-server,sql-server-2008,ms-access,user-defined-functions

Whenever I have to calculate a field and use the results in a second field, I use a common table expression to make the first calculation. Given this query, it could look like this: with cte_preprocess as ( SELECT tbl_ShiftSched.Shift, tbl_ShiftSched.SortInd, [ShiftStart]+ case when @dteFTm>[shiftstart] then DateAdd(day,1,@dteShf) else @dteShf end...

Suppress tempdb message when outputting result set

sql-server,tsql,sqlcmd

Change your if condition to the following pattern: IF 0 < OBJECT_ID('tempdb..#TheTable') DROP TABLE #TheTable This should not result in any error messages....

SQL query for details sum on each [closed]

sql,sql-server,sql-server-2008

[Sample data] [output] m | price m | price | total -----+-------- -----+-------+------- jan | 2000 jan | 2000 | 100000 feb | 5000 feb | 5000 | 100000 ... : ... ... : ... : ... dec | 4000 dec | 4000 | 100000 Query is: SELECT m, price,...

One Table - Two Fields - Same value - T/SQL

sql-server,tsql,sql-server-2012

The problem is that you're comparing both of the lines to themselves. I assume you would like to find just the first row, but also the second line (t1) will match to the first line (t2) and that's the second result you get. Not sure what you're doing, but you'll...

copy table and drop it

sql,sql-server,sql-server-2008,tsql,stored-procedures

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl2')) BEGIN -- tbl2 exists, so just copy rows INSERT INTO tbl2 SELECT * FROM tbl1; END ELSE BEGIN -- tbl2 doesn't exist, so create new table tbl2 and copy rows SELECT * INTO tbl2 FROM tbl1; DROP tbl1; END This...

Improving work with SQL DataTime

sql,sql-server,database,tsql

You can do it like this: SELECT IIF(DAY(@A) >= 25, DATEADD(d, 25 - DAY(@A), @A), DATEADD(d, 25, EOMONTH(@A, -2))) Here's a sample fiddle as well: sqlfiddle Note: EOMONTH requires SQL Sever 2012 or above - it returns the End-Of-Month date given a start date and a month offset....

How to join result of a Group by Query to another table

sql,tsql

You have mistake here from tblUsers u,a.Login_Name try to move this piece of code a.Login_Name to select Select u.User_Id, a.Login_Name from tblUsers u inner join (SELECT s.Login_Name Login_Name, COUNT(s.s1CIDNumber)as abc FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name) a on u.Login_Name=a.Login_Name ...

Concatenated string as parameter argument value does not give output in stored procedure

sql-server,sql-server-2008

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 ) ) to this: and...

How do I convert this tSQL statement to LINQ using group by in a sub query

c#,sql-server,linq,tsql

I think there is opportunity to rewrite your query, but for information purposes I rewrote your sql into linq verbatim. If you explain what you are trying to achieve we can provide alternative sql / linq var eqnums = new[] { "M0435", "Z0843" }; var testdate = "2008-06-01"; var query...

Sql inner join with three tables

sql,sql-server-2008

Based on the schema you are providing, I will assume that you find all the products of each document based on 1. which document_group the document is in 2. which product_type the document_group is associated with If that is the case, this is what your query would look like: SELECT...

T-SQL Ordering a Recursive Query - Parent/Child Structure

sql,tsql,recursion,order,hierarchy

The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting....

How can I create missing date records for each employee based off a limited calendar?

mysql,sql,tsql

Create a table that contains all the possible dates in the range, @alldates Then insert your missing records with something like this: Query INSERT INTO dbo.timeclock SELECT id ,d.punchtime ,'no time entered' FROM ( SELECT DATE ,id FROM @alldates d CROSS JOIN ( SELECT DISTINCT id FROM dbo.timeclock )...

How to display character values for numerical data for many columns

sql,tsql

Select * from Questions left join labels as l1 on questions.Q1 = l1.Labs left join labels as l2 on questions.Q2 = l2.Labs left join labels as l3 on questions.Q3 = l3.Labs ...

TSQL update value with subquery

sql-server,tsql,sql-update,compare

Could you try this: MERGE TableA AS [Target] USING TableB AS [Source] ON [Target].[ID] = [Source].[ID] AND [Target].[Name ] = [Source].[Name] WHEN NOT MATCHED BY TARGET THEN UPDATE SET NameMod = 1; It is using the MERGE clause. If you do not like the clause, you can use CTE like...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

Use A Sub-Query With A Case Statement

sql-server-2008,case

While there are other ways and other improvements that could be made, you should be able to simple move part of your logic into a common table expressions (CTE): WITH cte AS ( select case when username in (select username from database0254.dbo.userinformation) then 1 else 0 end As [Active User]...

Access filter on time

sql-server,sql-server-2008,ms-access,access-vba,ms-access-2007

The problem is, that the time is in the SQL server stored as datetime. So the field in the linked table is a datetime. When the time is stored as a time(7) in the SQL server, the field in the linked table will get a text. And then the select...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like

WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...

Update recently added column in script

sql,tsql

Just put a GO on the line after the first ALTER: ALTER TABLE Certification.ProductEducationCreditTypeTemp ADD NumberOfCredit decimal(18,4) GO UPDATE Certification.ProductEducationCreditTypeTemp SET NumberOfCredit = convert(decimal(18,4), NumberOfCredits) ALTER TABLE Certification.ProductEducationCreditTypeTemp DROP COLUMN NumberOfCredits EXEC sp_RENAME 'Certification.ProductEducationCreditTypeTemp.NumberOfCredit', 'NumberOfCredits', 'Column' ...

Retrieving an XML node value with TSQL?

sql-server,xml,tsql,soap

Here is the solution: DECLARE @xml xml SELECT @xml = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <SOAP-ENV:Body> <webregdataResponse> <result>0</result> <regData /> <errorFlag>99</errorFlag> <errorResult>Not Processed</errorResult> </webregdataResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>' declare @table table...

Join SQL query Results and Get-ChildItem Results

sql-server,sql-server-2008,powershell

OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...

Creating a view with a column not in the base table

tsql

Well you have to make use of Count function and GROUP BY clause. Suppose you have student id and assignment id in your table: sId AsnId 1 1 1 2 2 1 2 5 2 8 3 2 3 4 Then following query will give you count of students working...

SQL loop through check

sql,sql-server,tsql,case

You have some bad design if you don't have some status field. Here is bruteforce solution: SELECT *, CASE WHEN [Cancelled Date] IS NOT NULL AND ([Cancelled Date] > [Indication Date] OR [Indication Date] IS NULL) AND ([Cancelled Date] > [Taken Date] OR [Taken Date] IS NULL) AND ([Cancelled Date]...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql

The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...

SQL Customized search with special characters

sql,sql-server,sql-server-2008

Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008

I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql

I'd suggest doing it like that: 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 INNER JOIN...

Implement reference key in SQL Server

sql-server,sql-server-2008

It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...

What is the execute command to use here

sql,sql-server,sql-server-2008,tsql

Have a look at this and tell me if this works for you - I've added a try catch to check if the command is timing out. Just few tips: I would always use a try catch because it'll tell you exactly what you need to know and why your...