Declare A Concatenated Value.
OMG i'm so stupid, i edited my original post instead of replying!!
I was wondering if there was away to write a stored procedure where I concatenate several columns to create a Phrase and use that Phrase as a new value to do a second search in another table.
Example.
Table 1.
Column1 (Modem) Column2 (Highspeed) Column3 (Black)
Concatenated to be MODEM HIGHSPEED BLACK.
The 2nd table has the concatenated value as it's own column and 2nd column for the number in Inventory.
Column1 (MODEM HIGHSPEED BLACK) Column2 (44)
View Complete Forum Thread with Replies
Related Forum Messages:
Nulls In Concatenated String
How do I prevent the following null 'Answer'?This SQL will return a null string for 'Answer' whenever the count is null either for 'subquery-1' or for 'subquery-2', even though the other is not null. I need a string in either case. It would be better to have 'Answer' be "f1=, f2=25" than to have nothing. It doesn't seem right that both COUNT's have to be non-null to get anything other than null for the concatenated 'Answer'. There ought to be a way for COUNT to return 0 in some cases where it now returns null. I'd expect/prefer an 'Answer' of "f1=0, f2=25" or maybe even "f1=<null>, f2=25".I expect I'd have the same problem with nulls even if I wasn't using subqueries.SELECT 'f1='+CAST(COUNT(subquery-1) AS VARCHAR)+', f2='+CAST(COUNT(subquery-2) AS VARCHAR) AS AnswerFROM table1WHERE condition=5GROUP BY fieldX
View Replies !
Two Strings To Be Concatenated Have Different Collation.
I'm using SQL Datatype SqlString, so I'm not sure what is going wrong...any help? Here is a shortened version of the issue: Code and then Full Error Message. Thanks in advance for any help VB CODE Code Snippet Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub MyProc(ByVal cAddress1 As SqlString, ByVal cCity As SqlString, ByRef sName As SqlString) Dim output As SqlString Dim space As New SqlString(" ") output = System.Data.SqlTypes.SqlString.Concat(cAddress1, space) sName = output End Sub End Class Error Message Msg 6522, Level 16, State 1, Procedure MyProc Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'MyProc': System.Data.SqlTypes.SqlTypeException: Two strings to be concatenated have different collation. System.Data.SqlTypes.SqlTypeException: at System.Data.SqlTypes.SqlString.op_Addition(SqlString x, SqlString y) at System.Data.SqlTypes.SqlString.Concat(SqlString x, SqlString y) at SqlServerProject1.StoredProcedures.MyProc(SqlString cAddress1, SqlString cCity)
View Replies !
Concatenated String For Each Row In A Query...
So I've run into another problem. I've figured out how to concatenate multiple rows into a single string my only problem is using that on another query with multiple rows...Basically what I'm trying to do is pull up information for each class a student has in his/her profile and while at it pull up any prerequisite classes that are associated with a certain class. So the final query would look something like this... StudClassID Completed Class ID Name Description Credits Prereq... rest are insignificant... 0 0 CSC200 Cool prog... blah.... 3 CSC160, CSC180 I get the concept of the coalesce and cast just i'm not understanding how to get it to work with each return on the main select...anyways below are the tables and my current query call... Code Snippet USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Student_Classes] Script Date: 03/31/2008 01:32:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Student_Classes]( [StudClassID] [int] IDENTITY(0,1) NOT NULL, [StudentID] [int] NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CreditID] [int] NULL, [Days] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Time] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Classroom] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Grade] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Semester] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Notes] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Completed] [tinyint] NULL CONSTRAINT [DF_Student_Classes_Completed] DEFAULT ((0)), CONSTRAINT [PK_Student_Classes] PRIMARY KEY CLUSTERED ( [StudClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_ClassID] GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_CreditID] FOREIGN KEY([CreditID]) REFERENCES [dbo].[Credits] ([CreditID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_CreditID] GO ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_StudentsID] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) GO ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_StudentsID] USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Prerequisites] Script Date: 03/31/2008 01:32:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Prerequisites]( [PrerequisiteID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Prerequisite] PRIMARY KEY CLUSTERED ( [PrerequisiteID] ASC, [ClassID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_ClassID] GO ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_Prereq] FOREIGN KEY([PrerequisiteID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_Prereq] USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF] GO /****** Object: Table [dbo].[Credits] Script Date: 03/31/2008 01:32:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Credits]( [CreditID] [int] IDENTITY(0,1) NOT NULL, [ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Credits] [tinyint] NULL, CONSTRAINT [PK_Credits] PRIMARY KEY CLUSTERED ( [CreditID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Credits] WITH CHECK ADD CONSTRAINT [FK_Credits_ClassID] FOREIGN KEY([ClassID]) REFERENCES [dbo].[Classes] ([ClassID]) GO ALTER TABLE [dbo].[Credits] CHECK CONSTRAINT [FK_Credits_ClassID] SELECT sClass.StudClassID ,sClass.Completed ,sClass.ClassID AS 'Class ID' ,c.LongName AS 'Name' ,c.Description ,cred.Credits ,(SELECT COALESCE(@prerequisites + ', ', '') + CAST(PrerequisiteID AS varchar(7))) AS 'Prerequisites' ,sClass.Grade ,sClass.Days ,sClass.Time ,sClass.Classroom ,sClass.Semester ,sClass.Notes FROM Student_Classes sClass INNER JOIN Prerequisites preq ON preq.ClassID = sClass.ClassID INNER JOIN Classes c ON c.ClassID = sClass.ClassID INNER JOIN Credits cred ON cred.CreditID = sClass.CreditID WHERE sClass.StudentID = 0 ORDER BY sClass.ClassID ASC
View Replies !
Concatenated Results In A Batch SP
I've got a system that's grown like Topsy over the years and is now being asked to do things it was never designed for. As such I'm having to attempt ever more workarounds. In a simple 3 column table tbl1 ( SSN char(9), reason varchar(50), group char(1) ) I have a batched stored proc that selects rows based on the SSN select reason, group from tbl where group = 'I' AND ssn = @ssn; select reason, group from tbl where group = 'C' AND ssn = @ssn; select reason, group from tbl where group = 'N' AND ssn = @ssn; Now they want to group the rows and I'd really like to know beforehand how many and which groups to expect. So, I need to add a 4th batch select to the top of the statement that would return a up to 3 char string that indicates which groups are following. Something like IN or IC or CN, etc. This should be a trivial query but I can't figure it out. Thanks.
View Replies !
How To Do Separate The Concatenated String
declare @filter varchar(100) set @filter = '10,''firststring''||10,''secondstring''' declare @tbl table (id decimal, name varchar(20)) insert into @tbl values (substring(@filter,0,patindex('%||%',@filter))) hai in the above exmaple, i recieve input value (@filter) as concated string . pipeline(||) is my delimiter.. i want to split the string based on this delimater and need to insert into @tbl.. There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. What is the error in this. i believe i can do this way to insert to concatinated values. Help pls
View Replies !
How To Put New Data Into SqlDataSource Update With Concatenated Key?
Here is the problem. I have table such as this PK selection VARCHARPK selection_choice VARCHAR show BIT When I use the sqlDataSource to create an update it creates this.UPDATE lu_selection_choices SET show = @show WHERE (selection = @selection) AND (selection_choice = @selection_choice) Now this is OK, but when you perform an update and in say a details control you change the selection_choice the value of the parameter @selection_choice is going to be equal to the old data. So it performs a successful update on show which isn't being changed selection_choice is. So the questions is. Is there anyway to tell the update function of the datasource to insert the a new parameter with the SET selection_choice = @SOME NEW PARAM slection_choice? Thanks, Darren King
View Replies !
Pass Concatenated String To SPROC
Hello, We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values. My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string? Will I have to create Dynamic SQL to do this?...or... can I do something like this... @ConcatenatedString --eg. 1,2,3,4,5,6,7 SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @ConcatenatedString Thanks for your help.
View Replies !
Search On Concatenated Fields In Sql Server
Greetings,I used to do this kind of query in Ingres, where I concatenate(+)various fields as one field and search accordingly.Is there an equivalent method in SQL server ?SELECT a.rsrcid,a.rsrchqnumber,c.perslastname,c.persfirst name,b.asgtid,b.asgtactualstartdate,b.asgtactualenddate ,CASE b.enumstateWHEN '2' THEN 'Running'WHEN '3' THEN 'Cancelled'WHEN '4' THEN 'Closed'WHEN '6' THEN 'Open'END AS statusFROM pblocal.dbo.resources aINNER JOIN pblocal.dbo.assignments b ON b.asgtrsrcguid = a.rsrcguidINNER JOIN pblocal.dbo.persons c ON c.persguid = a.rsrcpersguidWHERE a.rsrcid+a.rsrchqnumber+c.perslastname+c.persfirst name not in(SELECT e.rsrcid+e.rsrchqnumber+g.perslastname+g.persfirst nameFROM dtlocal.dbo.resources eINNER JOIN dtlocal.dbo.assignments f ON f.asgtrsrcguid = e.rsrcguidINNER JOIN dtlocal.dbo.users h ON h.userguid = e.rsrcuserguidINNER JOIN dtlocal.dbo.persons g ON g.persguid = h.userpersguid)
View Replies !
How To Extract Data From A Concatenated Field
Hi everyone. I need help extracting information from a concatenated field delimited by a '/' character. I know that the logic is to basically find the position of the '/' character and use the substring function to extract the data but I am unfamiliar with how to do this in SQL Server 2000. Please see examples below. Thanks in advance. FIELD ---------- 1234/4567 2345/6754 1234 I need to extract the data before and after the '/' character. There are records however with on the leftmost part of the data such as row number 3. FIELD 1 FIELD 2 ------------------ 1234 4567 2345 6754 1234
View Replies !
Concatenated Column Returns Null
Hi folks,I have an issue with a column I created in my query called Instance.SELECT Object_Id, Event_type, Audience, Subject, Provider, Academic_Year, Start_date, End_date, CONVERT(varchar, Academic_Year) + ' (' + CONVERT(varchar, Start_date, 103) + ') : ' + Event_type AS InstanceFROM EventsORDER BY Event_type Above is my query. The problem is because the start date column can be null, it also returns the Instance column as null for that row.I thought it would have just missed out the date and display the rest, but it doesn't.Is there any way I could get the Instance column to display a value, when the start date is null?ThanksEdit: Managed to sort it using ISNULL()
View Replies !
Complex Concatenated String For An 'exec(@sql) '
I have: <<Select @SQL = 'Select Keyword, SICCode From zlk_SICCodeKeyword Where Keyword like ''%' + @KeywordOrSIC + '%''' + ' order by Keyword' exec(@SQL)>> which works fine, but I want to modify it to do this <<Select Replace(Keyword,',',' ') AS Keyword, SICCode From zlk_SICCodeKeyword Where Keyword like 'real%' order by Keyword >> which works from the query window but I can not get the right combination around the 'replace section' to make up a string for the exec. All help greatly appreciated Judith
View Replies !
Problem With Two Strings To Be Concatenated Have Different Collation???
Hi all, I am having a little problem to use CLR Function inside SqlServer 2005. I do not see any problem with my code in C# as well as SqlServer sides. Please help if you know the solution. /************************C# Codes***************************/ using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public class ClrUDF { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString GetString(SqlString str) { SqlString tmp = new SqlString("Hello "); tmp += str; return tmp; } }; /********************************************************/ CREATE ASSEMBLY [SqlClr] AUTHORIZATION [dbo] FROM 'C:ProjectsSqlClrSln..... SqlCrl.dll' WITH PERMISSION_SET = SAFE Create FUNCTION dbo.GetString(@str as nvarchar(20)) RETURNS nvarchar(200) AS EXTERNAL NAME SqlClr.ClrUDF.GetString Try to use the function: Select dbo.GetString('Jame') and the error is throw Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'GetString': System.Data.SqlTypes.SqlTypeException: Two strings to be concatenated have different collation. System.Data.SqlTypes.SqlTypeException: at System.Data.SqlTypes.SqlString.op_Addition(SqlString x, SqlString y) at ClrUDF.GetString(SqlString str)
View Replies !
Importing CSV - Extra Fields Concatenated
I have designed an SSIS package and in a data flow task I've defined my source and destination components and mapped all the fields. The task works fine as long as I have the same number of fields in my CSV file as what I have defined in the task. The issue is that if my CSV contains more fields than what are declared in the mapping, then the extra fields at the end of each line are concatenated into the last column defined in my map. For instance: CSV SQL Product -> Product M0 -> M0 M1 -> M1 M2 -> M2 This works fine, but if my CSV file looks like this: Product M0 M1 M2 M3 M4 The values in fields M3 and M4 are concatenated with M2 and all 3 are imported into field M2 in my sql table. Any ideas?
View Replies !
MAX Doesn't Apply For Concatenated Variables?
If I load up a local variable defined as varchar(MAX) with a 10,000-character continuous string, i.e. '123456789.....100000' the variable retains the full value as expected. However, if I break that string up into '123456789....5000' + '5001...10000', the variable is truncated at 8000 characters. Is there a way around this? Thanks.
View Replies !
Change Format Of Concatenated Columns
Is there a way to have my output return this, "$-2,778.90 $$$ IN NETWORK DISCOUNT", instead of what I am currently getting which is this "-2779 $$$ IN NETWORK DISCOUNT" using: LTRIM(STR(ar_billtrans_payadj.amount)+ ' ' + '$$$' + ' ' + ut_journalcode.[description]) AS PPmtTypeDesc ar_billtrans_payadj.amount datatype is decimal ut_journalcod.[description] datatype is varchar
View Replies !
Setting Default Value (concatenated String) Of Column Using UDF
Hello, I'm trying to set the default value of a column (SysInvNum) in a table (caseform) of mine by concatenating 3 other fields in the same table. These other fields are all Integer datatypes. they are "CaseYear" e.g. (2005), "InvNum" e.g. (0001) and "PostId" e.g. (5). So basically the SysInvNum column for this row should read '200500015' When I run a basic query using the CAST or CONVERT functions like this: SELECT convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) from caseform OR SELECT cast(caseyear as varchar(4)) + cast(InvNum as varchar(4)) + cast(postid as varchar(1)) from caseform I get the results I want. But since I want this value to be the default value of the column, I tried inserting this: convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) into the default value parameter of the column in the caseform table. The result is a string that is the query itself. I then tried creating a UDF called getsysinvnum() where I declare and set 2 variables whilst returning one of the variables as a varchar. An example of what it looks like is this: CREATE FUNCTION GetSysInvNum() RETURNS varchar AS BEGIN DECLARE @maxcaseid Int DECLARE @sysinvnum varchar SELECT @maxcaseid = max (caseid) from caseform SELECT @sysinvnum = cast(caseyear as varchar(4)) + cast(invnum as varchar(4)) + cast(postid as varchar(1)) from caseform where caseid = @maxcaseid RETURN @sysinvnum END The result I get when I plug this into the default value of the column as : ([dbo].[getsysinvnum]()) is "2". Yes it returns the number "2" could someone please tell me what I am doing wrong, or suggest a better way for me to do this? Thanks in advance 'Wale
View Replies !
MSMerge_genhistory And MSMerge_replinfo (comparing Int With Concatenated VarBinary)
This may be one of those, "why the h3ll is he trying to do THAT?" type questions. Please bear with me (or ignore and move on)... Background: I have a horizontally partitioned, dynamic merge publication that will be shared out with up to 60 subscribers. Things are going swimmingly, but I would like to get a more refined view of the merge update process; specifically, I am trying to query MSMerge_genhistory to see what updates have been made, which subscribers have received those updates, and what updates are pending. MSMerge_genhistory contains a field nicknames (Varbinary(1000) which is a concatenated value of subscribers. The "official" definition (from the SQL 2000 system table map) is A list of nicknames of other Subscribers that are known to already have this generation. Used to avoid sending a generation to a Subscriber that has already seen those changes. Nicknames in the nicknames list are maintained in sorted order to make searches more efficient. If there are more nicknames than can fit in this field, they will not benefit from this optimization. The MSMerge_replinfo table contains a field replnickname (int) which contains a unique integer value that, when cast as a Varbinary, matches the values that are in the nicknames field in MSMerge_genhistory. Compressed nickname for the replica. So, my question is: how do I compare the varbinary(1000) (with concatenated values) with the int (cast as a varbinary). Examples: MSMerge_Genhistory: Column nicknames: 0x677112FA 0x677112FAEAEB0704677112FA MSMerge_Replinfo: replnickname cast(replnickname as varbinary(1000)) 17354636740x677112FA -3536959960xEAEB0704 * Note that EAEB0704 is in the second Varbinary value from MSMerge_Genhistory, but not in the first. If there's a simpler way to do this (ie, a stored proc/sp_help*) that I have overlooked, please do enlighten me. Regards, Hugh
View Replies !
Concatenated Long Varchar Fields Are Getting Truncated
(I'm using MS SQL 2000) I've two tables that I've made from some query subsets. Each table has a varchar field with notes/memos and I want to concatenate the fields into one long field. The problem I'm running into is that when I run the query to check the concatenation, the field is truncated maybe 256 chars in. I tried converting and casting the field as nvarchar 4000, and I've also done the same for the fields in the two tables, but that doesn't seem to help. I can query for the fields from each table and none of them are truncated by themselves. It only happens after I concatenate them. I've created a new table and inserted the results into it, but the field in it is also truncated. Am I missing something obvious here?
View Replies !
Displaying Multiple Values In A Concatenated String
SQL Server 2005.(SP2). MS SSRS; I want to display some numbers in the same line as a concatenated string. For example a Customer may have multiple bills. These bill numbers are displayed in separate rows. I want to display them all on the same line. Example of current display: Customer Bill # ABC Company 123 ABC Company 456 ABC Company 789 etc I want this to display as below: Cusotmer Bill # ABC Company 123, 456, 789, etc. Is this possible in SSRS. Please help me with the syntax. Thanks in advance.
View Replies !
Error Passing Concatenated String To Proc
Hi, I have a stored proc which accepts a varchar(255) as a parameter and when I call the proc using a concatenised string I get an error i.e. -- Proc CREATE PROCEDURE #proc_param_test @p_param1 varchar(40) = NULL , @p_param2 varchar(40) = NULL AS BEGIN SELECT @p_param1, @p_param2 END EXEC #proc_param_test 'test', 'test 2' returns ---------------------------------------- ---------------------------------------- test test 2 but EXEC #proc_param_test 'test', 'test 2' + ' - the rest' gives a Incorrect syntax near '+'. error The solution must be a real doddle but it's a 'mare to find anywhere. Cheers, John
View Replies !
Difficult Query: Return Recordset From Concatenated Strings?
Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray
View Replies !
Concatenated String Of Comma Separated Values (was &"Help With Query&")
I have following 2 queries which return different results. declare @accountIdListTemp varchar(max) SELECT COALESCE(@accountIdListTemp + ',','') + CONVERT(VARCHAR(10),acct_id) FROM (SELECT Distinct acct_id FROM SomeTable) Result print @accountIdListTemp The above query return the values without concatenating it. declare @pot_commaSeperatedList varchar(max) SELECT DISTINCT acct_id into #accountIdListTemp FROM SomeTable SELECT @pot_commaSeperatedList = COALESCE(@pot_commaSeperatedList + ',','') + CONVERT(VARCHAR(100),acct_id) FROM #accountIdListTemp print @pot_commaSeperatedList drop table #accountIdListTemp This query returns result as concatenated string of comma separated values. If i want to get similar result in a single query how can i get it?
View Replies !
Declare @var????
I am trying to get a grasp on the Sql Stored procedures it seems i dont really understnad what DECLARE @Date DateTime means??? I mean i think it means that i am just declaring a varible name Date that will hold a DateTime Value??? is that correct or is it more to it???? CREATE PROCEDURE dbo.Tracking_GetStatus AS DECLARE @Date DateTime DECLARE @Begining DateTime DECLARE @Ending DateTime SET @Date = GETDATE() SET @Begining = DATEADD(ss,(DATEPART(ss,@Date)*-1), DATEADD(mi,(DATEPART(mi,@Date)*-1), DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date))) SET @Ending = DATEADD(ss,-1, DATEADD(dd,1,DATEADD(ss,(DATEPART(ss,@Date)*-1), DATEADD(mi,(DATEPART(mi,@Date)*-1), DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date))))) SELECT Vehicl, UpdateTi XCoord, YCoord, Status FROM Track WHERE UpdateTime >= @Begining AND UpdateTime <= @Ending RETURN GO
View Replies !
DECLARE In SQL CE
Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints? Matt
View Replies !
My DropDownList Won't Declare?
Need a little help! I am trying to insert ListItems values from a DropDownList into a database table. However in the code behind I am continuosly met with the error Name 'ddltest' is not declared. As you can see from the code below ddltest is an object with the ID ddltest. What am I doing wrong? Protected Sub ddltest_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)Dim MyVar As String = ddltest.SelectedItem.Value If MyVar = "" Then ErrorMessage.Text = "Please select a test" Else 'Insert selection into databaseDim oConnection As New SqlConnection Dim oCommand As SqlCommand Dim sConnString As String Dim sSQL As String sConnString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|xxxxx.mdf;Integrated Security=True;User Instance=false"oConnection = New SqlConnection(sConnString) sSQL = "INSERT INTO testDB(Myxxxx) Values (@Myxxxx)" oConnection.Open()oCommand = New SqlCommand(sSQL, oConnection)oCommand.Parameters.Add(New SqlParameter("@Myxxxx", MyVar)) oCommand.ExecuteNonQuery() oConnection.Close() ErrorMessage.Text = "You selected " & MyVar & " and it has been added to the database." End If End Sub <asp:TemplateField HeaderText="Test"> <EditItemTemplate> <asp:DropDownList ID="ddltest" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddltest_SelectedIndexChanged" > <asp:ListItem Selected="True" Value="" ><-- Please Select a test --></asp:ListItem><asp:ListItem Value="1">1</asp:ListItem> <asp:ListItem Value="2">2</asp:ListItem> <asp:ListItem Value="3">3</asp:ListItem> <asp:ListItem Value="4">4</asp:ListItem> <asp:ListItem Value="5" >5</asp:ListItem> </asp:DropDownList> </EditItemTemplate> </asp:TemplateField>
View Replies !
Must Declare Scalar @.....
Hi everyone, I am getting that infamous message on an INSERT Sql query. I am doing everything right by the looks of it. All variables are either passed in through a custom form, or else declared and initialised in the body of the script. I post the relevent code below: SQLsqlInsertEmail = "INSERT INTO CandidateLogins (SiteID, LoginName, CandidateEmail, DateRegistered) " & _" VALUES (@SiteID, @LoginName, @CandidateEmail, @DateRegistered); SELECT SCOPE_IDENTITY()"Try sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@SiteID", SqlDbType.Int)) sqlSetCandidateEmail.Parameters("@SiteID").Value = SiteID sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@LoginName", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@LoginName").Value = userName sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@CandidateEmail", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@CandidateEmail").Value = email sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@DateRegistered", SqlDbType.DateTime)) sqlSetCandidateEmail.Parameters("@DateRegistered").Value = DateRegistered sqlSetCandidateEmail = New SqlCommand(sqlInsertEmail, C4LConnection) C4LConnection.Open() CandidateID = sqlSetCandidateEmail.ExecuteScalar() Catch Exp As SqlException lblResults.Visible = True lblResults.Text = "Unable to Register Jobseeker: " & Exp.MessageFinallyC4LConnection.Close()End Try All the variables passed into the SQL statement are initialised, with SiteID beign set to '0', rather than Null (none of the fields are Nullable in the database table) and I have checked that the SqlDbType's correspend to the Table Definition So far as I can discern, everything is correct and as can be seen, I am not using a stored procedure in this instance, but the script falls over be producing the error message "Must Declare Scalar @SiteID", even though SiteID is declared as Int32 further up in the script. Any help would be appreciated.
View Replies !
'declare' Syntax In A UDF
Hi, I'm trying to create a function that returns a table, however I wantto use a local variable in there and enterprise manager ain't liking it!The error I get is number 156 'incorrect syntax near the keyword'declare'.. hopefully this is just a simple thing where I've put it inthe wrong place.The code follows:CREATE FUNCTION AFGroupedTotals (@campaign nvarchar(30),@datefromsmalldatetime, @dateto smalldatetime, @prospect nvarchar(30), @typenvarchar(20))RETURNS TABLE ASRETURNdeclare @set nvarchar(150)select "Total Pledged" as info, sum(total) as totFROM AFresponseTotals (@campaign, @datefrom, @dateto,@prospect)Cheers for any help,Chris
View Replies !
Declare @var Text
Hi, I want to store more than 8000 characters. Ideally it might have been fine if decalre @var text had worked for me. Can anyone tell is it possible.... thanks, sajai.
View Replies !
DECLARE CURSOR
Is there any way to create a cursor, based on a dynamically created select_statement? Something like: DECLARE someCRS CURSOR LOCAL FAST_FORWARD FOR @strSelect where @strSelect is previously declared as let's say varchar. I don't want to create a stored procedure for this. Thanks!
View Replies !
Declare Variable For All In SP
In a previous life, for each variable that we passed into a query, we would set -1 to the default for all so that when we converted it to an SP, we could query a specific dataset or or all. The following is a sample bit of code, I can not for the life of me remember how to pull back all using -1. The following is the code that I currently have, it's a simplified version of the total SP that I am trying to use, but enough to give you the idea of what I am trying to do. The MemberId field is a varchar(20) in the table. Create procedure sp_GetClaims_BY_MemberID @Memberid varchar (50) as Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible where Membid = @memberid EXEC sp_GetClaims_BY_MemberID '99999999999' The above SP works fine, I just need to be able to modify it so that I can pull back all records for all member id's, any suggestions? I am currently working in SQL 2000.
View Replies !
Help With Declare And Cursor
I keep getting the message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'declare'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'declare'. What am I doing wrong? declare @dbname varchar(8000), declare @countyname varchar (200) , declare @sql varchar(8000) declare county_name cursor for select distinct county from Zipcodes open county_name fetch next from county_name into @countyname declare dbname_name cursor for select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter' open dbname_name fetch next from dbname_name into @dbname WHILE @@FETCH_STATUS = 0 BEGIN set @sql = ' select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'+@countyname+' from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip' exec (@sql) end set @sql = '' fetch next from dbname_name into @dbname fetch next from county_name into @countyname CLOSE county_name DEALLOCATE county_name CLOSE dbname_name DEALLOCATE dbname_name
View Replies !
Declare Variable
Declare @DBName varchar(25) select @DBName = 'Production' Select @DBName = @DBName + '.dbo.'+'sysfiles' select @DBName select * from @DBName When I executes above lines of code in query analyser it give me an error like : Server: Msg 137, Level 15, State 2, Line 5 Must declare the variable '@DBName'. give me solution as soon as possible Thanks Aric
View Replies !
Why Declare Does Not Work
declare @fdas as varchar(10) set @fdas = 'master' use @fdas i try to execute this one it does not work said that Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@fdas'. how to fix that one? by not replacing the @fdas but i got master database ------------ what is the difference actually between use master than the first querry? arifliminto86
View Replies !
Declare Table
Dear All, actually, in our application, we are using so many temporary local variables like @table_name.... and based on these local variables, we are making joins with the regular adtabase tables. is it better thing instead of temp tables? what exactly i want to know is, @table_name,#temp_table,##temp_table..... in these three processes, which one is the best one... thanks in advance Vinod Even you learn 1%, Learn it with 100% confidence.
View Replies !
Declare Table
When I run this script.It gives me an error that I need to "Delcare @pc2" Why? Please help. Declare @pc2 Table ([prop_char_typ_code] [varchar](5) NOT NULL, [tax_year] [varchar](4) NOT NULL, [property_id] [int] NOT NULL, [id] [int] IDENTITY(1,1) NOT NULL, [value] [varchar](100) NULL, [prop_segment_id] [int] NULL) Insert into @pc2 ([prop_char_typ_code], [tax_year], [property_id], [id], [value], [prop_segment_id]) Select [prop_char_typ_code], [tax_year], [property_id], [id], [value], [prop_segment_id] from property_char Go SELECT 'ACRES', '2008', property_char.property_id, ROUND(property_char.value,0) FROM property_char INNER JOIN property ON property_char.property_id = property.id INNER JOIN property_char AS @pc2 ON property.id = @pc2.property_id INNER JOIN prop_valuation ON property.id = prop_valuation.property_id INNER JOIN val_component ON property.id = val_component.property_id WHERE property_char.property_id < 81695 AND property_char.property_id = property.id AND property_char.prop_char_typ_code = 'SIZE' AND property_char.tax_year = '2008' AND @pc2.prop_char_typ_code = 'USECD' AND (@pc2.value not in ('85','86','87','88','95') AND --( <=== Review list of Usecodes)) @pc2.tax_year = '2008' AND @pc2.property_id = property.id AND property.pact_code = 'REAL' AND (property.eff_to_date is null OR property.eff_to_date >= getdate())AND prop_valuation.property_id = property.id AND prop_valuation.tax_year = '2008' AND prop_valuation.local_assed_ind = 'Y' AND val_component.value_type = 'MKLND' AND val_component.property_id = property.id AND val_component.tax_year = '2008' AND val_component.modified_value > 0)AND NOT EXISTS (Select 'z' from parcel_exclude where property.parcel_number = parcel_exclude.parcel_number AND special_assessment = 'CD')
View Replies !
Must Declare The Scalar Variable
Hi with the code below I am getting the error Error inserting record. Must declare the scalar variable "@contractWHERE" I removed @contract and it then gave me the error Error inserting record. Must declare the scalar variable "@zipWHERE" I was wondering if some can point me in the right direction for fixxing this protected void cmdUpDate_Click(Object sender, EventArgs e) { //Define ADO.NET Objects. string updateSQL; updateSQL = "UPDATE Authors SET "; updateSQL += "au_id=@au_id, au_fname=@au_fname, au_lname=@au_lname, "; updateSQL += "phone=@phone, address=@address, city=@city,state=@state, "; updateSQL += "zip=@zip, contract=@contract"; updateSQL += "WHERE au_id@au_id_original"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(updateSQL, con); //Add the parameters. cmd.Parameters.AddWithValue("@au_id", txtID.Text); cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text); cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text); cmd.Parameters.AddWithValue("@phone", txtPhone.Text); cmd.Parameters.AddWithValue("@address", txtAddress.Text); cmd.Parameters.AddWithValue("@city", txtCity.Text); cmd.Parameters.AddWithValue("@state", txtState.Text); cmd.Parameters.AddWithValue("@zip", txtZip.Text); cmd.Parameters.AddWithValue("@contract", Convert.ToInt16(chkContract.Checked)); cmd.Parameters.AddWithValue("au_id_original", lstAuthor.SelectedItem.Value); //Try to open the database and execute the update try { con.Open(); int updated = cmd.ExecuteNonQuery(); lblStatus.Text = updated.ToString() + " records inserted."; } catch (Exception err) { lblStatus.Text = "Error inserting record. "; lblStatus.Text += err.Message; } finally { con.Close(); } } } Many Thanks in advance
View Replies !
Must Declare The Scalar Variable
I'm making an ecommerce web app from following the Apress "Beginning ASP.Net 2 E-commerce with C#" book, and when I implement a stored procedure (I made a mdf DB in the app_Data folder), I get the following message: Must declare the scalar variable @CategoryIDThe code used to obtain this error is below: CREATE PROCEDURE DeleteCategory(@CategoryINT int)ASDELETE FROM CategoryWHERE CategoryID = @CategoryID I get this error with every Stored Procedure I try to implement. What should I do to fix this? In SQL Server 2k5 Management Studio, this problem does not present itself.
View Replies !
How To Declare Global Sqlcommands...
Hello guys! Is it possible to declare global sql commands and call it in a rowcommand_function? Here's what I did... Dim p_s_syounin2 As New SqlCommand Dim cnn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString) Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then p_s_syounin2.CommandText= ("UPDATE TE_shounin_zangyou SET p_s_syounin2=syain_hnm FROM TR_syainID WHERE syozokubu_id=20 AND syozokuka_id=21 AND kaikyuu_id=23") '''' connection string is not placed here acc. to my research End If End Sub Protected Sub my_gridview_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles my_gridview.RowCommand If e.CommandName = "Approve" Then cnn.Open() p_s_syounin2.ExecuteNonQuery() cnn.Close() End If End Sub I get an error message that says " ExecuteNonQuery: Connection property has not been initialized. " Please help me. Thanks guys. Audrey
View Replies !
Must Declare The Scalar Variable
Hello! I have a aspx page in which I have a Gidview populated by a sqlDataSouce. This is my code: <%@ Page Language="VB" AutoEventWireup="false" CodeFile="CostEmployee1.aspx.vb" Inherits="RecursosHumanos_CostEmployee1" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Style="z-index: 100; left: 0px; position: absolute; top: 0px"> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <Columns> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Editar" Text="Editar" runat="server" CommandName="Edit"></asp:LinkButton> </ItemTemplate> <EditItemTemplate> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Actualizar" style="color: white"></asp:LinkButton> <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancelar" style="color: white"></asp:LinkButton> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Apagar" Text="Apagar" runat="server" CommandName="Delete" OnClientClick='return confirm("Tem a certeza que deseja apagar este registo?");' CausesValidation="false"></asp:LinkButton> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_CostEmployee" InsertVisible="False" SortExpression="Id_CostEmployee"> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Id_CostEmployee") %>'></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Id_CostEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_Employee" SortExpression="Id_Employee"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="FullName" SortExpression="FullName"> <EditItemTemplate> <asp:TextBox ID="textbox5" runat="server" Text='<%# Bind("FullName")%>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Bind("FullName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="NumEmployee" SortExpression="NumEmployee"> <EditItemTemplate> <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label7" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Period" SortExpression="Period"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Period") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("Period") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="CostHour" SortExpression="CostHour"> <EditItemTemplate> <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("CostHour") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Bind("CostHour") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Date" SortExpression="Date"> <EditItemTemplate> <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Date") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Bind("Date") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#EFF3FB" /> <EditRowStyle BackColor="#2461BF" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EuroscutConnectionString %>" SelectCommand="SELECT [HR.CostEmployee].Id_CostEmployee, [HR.CostEmployee].Id_Employee, [HR.CostEmployee].Period, [HR.CostEmployee].CostHour, [HR.CostEmployee].Date, [HR.Employee].FullName, [HR.Employee].NumEmployee FROM [HR.CostEmployee] INNER JOIN [HR.Employee] ON [HR.CostEmployee].Id_Employee = [HR.Employee].Id_Employee" UpdateCommand="UPDATE [HR.CostEmployee] set Period = @Period, CostHour = @CostHour where Id_CostEmployee = @Id_CostEmployee" DeleteCommand="DELETE from [HR.CostEmployee] where (Id_CostEmployee = @Id_CostEmployee)"> <UpdateParameters> <asp:Parameter Name="Period" /> <asp:Parameter Name="CostHour" /> <asp:Parameter Name="Id_CostEmployee" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="Id_CostEmployee" Type="int32" /> </DeleteParameters> </asp:SqlDataSource> </div> </form></body></html> When I run the page I'm able to edit the row but when I try to delete it gives me the error: Must declare the scalar variable "@Id_CostEmployee". I'm tired of "googling" this error, and I've tried all the advices, nothing... I don't know what is happening here, I have 5 other forms, all simillar and they all work! Any suggestions, pleeeeaaaase? Thank's! Paula
View Replies !
Must Declare The Scalar Variable
Hi, I found a nice tutorial on the net on how storing images to a SQL DB (http://blogs.msdn.com/jdixon/articles/495408.aspx) but have some troubles in retreiving the image back from the database. Once I click the View details link, I receive an error message "Must declare the scalar variable @..." Can someone tell me how I can define this scalar variable and/or how I can display an image from my database ? Thanks to all !!! Bart
View Replies !
Declare Variable Dynamically
I'm attempting to modify some Crosstab generating code, and I need someadvice/examples.Currently, the code uses a single string variable to store thedynamically generated query (www.johnmacintyre.ca). The problem is thatI am trying to pivot biological taxonomy information, and may end upwith a table containing over 200 columns. This takes the dynamic stringwell over the 8000char limit for variables.[color=blue]>From my understanding, the EXEC() command does not have the 8000char[/color]limit if the execution string is broken into chunks, and concatenatede.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need isto:1) start a counter at the beginining of the dynamic generation2) append the counter value to the end of a string variable name3) DECLARE the new variable and attach that loop cycle of text to it,or attach each chunk of characters < 80004) build the EXEC() string by concatenating each dynamic varibleCan this be done? Should it be done? Is there a better way to addressthis type of problem?Thanks for any ideas or insightsTim Pascoe
View Replies !
Need To Declare Multiple Values
Alright, so I have this problem. I want to make it easy for me andothers to be able to run a query and easily choose whether we wantMerchants or NonMerchants. Previously, we would have to comment outbits of code and would leave things messy (it would also leave room forerror). So, I'm thought DECLARE and SET would work. Wrong.This is what I have....DECLARE @Merchant VARCHARSET @Merchant = (Select CONVERT(VARCHAR, Id) + ','FROM AdminAdvertiserTypesDDLWHERE Id IN (1,3,4,5)) // Includes Active, Out of Business, Cease to do business, InactiveI've also tried...SET @Merchant = '1,3,4,5'Then, in the query itself I try:WHERE AdminAdvertiserTypesDDL.Id = @MerchantorWHERE AdminAdvertiserTypesDDL.Id IN @MerchantorWHERE AdminAdvertiserTypesDDL.Id IN (@Merchant)orWHERE AdminAdvertiserTypesDDL.Id LIKE @MerchantEither way, it will ONLY show me the merchants whose Id is 1. When Imake the query:WHERE AdminAdvertiserTypesDDL.Id IN (1,3,4,5)I finally get the desired results.Any ideas or tips?Thank you so much!
View Replies !
Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway? Is Declare Cursor same as Create Cursor and if not what is the major difference?
View Replies !
|
TRACKER
