Complex Concatenated String For An 'exec(@sql) '
Aug 10, 2000
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 1 Replies
ADVERTISEMENT
Sep 23, 2005
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 1 Replies
View Related
Oct 5, 2007
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 6 Replies
View Related
Mar 31, 2008
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 5 Replies
View Related
Jul 24, 2004
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 2 Replies
View Related
Jun 20, 2008
I have a table with multiple rows for a single reference, e.g.
Col1 Col2
1 John
1 Mary
1 Tom
2 Dick
2 Anne
How do I create this view:
Col1 Col2
1 John, Mary, Tom
2 Dick, Anne
View 5 Replies
View Related
Oct 23, 2005
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 10 Replies
View Related
Oct 17, 2007
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 2 Replies
View Related
Nov 8, 2007
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 4 Replies
View Related
Nov 21, 2006
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 4 Replies
View Related
Oct 24, 2005
I am trying to do an insert statement utilizing a variable string.
something like:
Set @cString = 'SELECT top 10 *
FROM OPENDATASOURCE(
' + char(39) + 'SQLOLEDB' + char(39) + ',' + char(39) +
'Data Source=' + @lServer + ';User ID=' + @user + ';Password=' + @pword + char(39) + '
).myServer..
Insert into #Temp_table (field1, field2)
select exec @cString
--What is the syntax for this?
View 3 Replies
View Related
Mar 7, 2001
I am using the EXEC function in order to specify which view to access. However I can not get the datediff to work with it....does anyone have any clues?
View 1 Replies
View Related
Aug 21, 1998
Hi All,
I am using SQL Server 7 Beta 3 and I am having trouble with using a variable
in an EXEC statement.
I have a stored procedure that is designed to retrieve a serial
number from a table and then increment it by one(1) and return the
original serial number to the calling function.
As long as the table name is hardcoded the following code works fine:
CREATE PROCEDURE IncrementSerialIDNo
(@serial_num_temp varchar(15), @serial_num_output varchar(15) output)
AS
BEGIN TRANSACTION
BEGIN
SELECT @serial_num_temp = tblSerialNo.SerialNo
FROM tblSerialNo
SELECT @serial_num_output = @serial_num_temp
UPDATE tblSerialNo
SET tblSerialNo.SerialNo =
CONVERT(varchar(15),(CONVERT(integer,@serial_num_t emp ) + 1))
IF (@@error = 0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
RETURN
However, if I change the table name to a variable that is passed to the
stored procedure as a argument, I need to use the EXEC statement.
My problem is that for the EXEC statement to work I need to escape the
variables and I run into a problem with the " = " ( equal sign ).
Example:
DECLARE @SerialTable as varchar(30), @CompanyNo as varchar (3)
SELECT @CompanyNo = `001`
SELECT @SerialTable = `tblSerialNo` + @CompanyNo
EXEC ( `SELECT ` +@serial_num_temp + `= SerialNo FROM ` +
@SerialTable )
Following is the error message that I keep getting:
Server: Msg 170, Level 15, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near `=`.
No amount of testing with different qoutes or doublequotes and such,
have made any difference.
I have tried numerous variations on the above SELECT statement with the
same end result of the error on the "=" sign.
I cannot seem to assign one variable to another using the "=" (equal sign)
in an EXEC statement.
Does anyone have example code of using the equal sign to assign one variable
to another in an EXEC statement or can someone point me in the right direction?
Thanks in advance for any and all help.
jeff alerta
jeff@nestworks.com
View 5 Replies
View Related
Dec 17, 2003
Hi.
I have been trying to find a solution to this for some time now. Was wondering if some1 had done is earlier and has a solution.
I have a 2 server machines.
Namely: ServerOne and ServerTwo
ServerOne (main server, On 1 machine.)
Table - Foofoo
ServerTwo (secondary server, on another machine)
Table - Booboo
I want to be able to link these two servers and work with them.
At the moment I do something like this.
NB. My Stored Procedure is on ServerOne
declare @server varchar(100)
Select @server=Servername from ServerOne.systemsettings where name='secondary'
-- @server is not equal to 'ServerTwo'
declare @str varchar(8000)
set @str = '
select *
from Foofoo f
join ' + @server + '.myDB.dbo.Booboo b on b.id = f.id '
exec(@str)
My problem is that this works fine but I do not like working with long strings and then executing them at the end.
I have also been told that SQL's performance on this is not entirely that well as normal select's would be.'
Another thing that could be used is SQl's own linked servers method but apparently out system was designed some time ago and a lot of things have been developed around the current technic.
Our server names also change quite frequently making hadcoding server names difficult.
Using the string exec convention also hides from sql when you do a dependency search of a particular table.
Is there a way I can save the server name on @server and then just add it to the select stmt without using the long stringing idea.
Any feedback with ideas and solutions will be greatly appreciated.
Bhit.
View 1 Replies
View Related
Aug 1, 2000
I am trying to make up a SQL string which will be executed with the Exec command
I want to add a return column that is not in the table, and the table columns:
something like
Select @Ten As Ten, @Tablename.* From @Tablename (
where @Ten has an integer value. )
The statement was originally:
select @SQL = 'select * from ' + @TempName
exec (@SQL)
which had no problem. Then I needed to add an extra column of static data to all the returned rows, and confusion !!!!!
Thanks,
Judith
View 1 Replies
View Related
Nov 23, 2006
Hey guys, needless to say I'm new at this.
What I'm trying to accomplish is to execute a SQL string via exec and inside it set the value of a local variable. I understand that I cannot do this the way I'm currently doing it because an Executed string runs in a scope of its own so local variables are invisible. And sure enough this is the error I get. So how do I make this work?
Code snip:
declare @ErrMessage as varchar(1000)
set @Sql = 'if exists ( select * from ' + @TargetTable + ' where (' + @ValueField + '=' + '''' + @NewValue + ''''
set @Sql = @Sql + ' and ' + @TagField + '= ' + '''' + @Tag + '''' + '))' + @CRLF
set @Sql = @Sql + 'set @ErrMessage = ''Insertion could not be performed. ' + @NewValue + ' is already an entry in the table. '''
So what I want is check if a certain table has entries...what table? I don't know, there are tens that this check will apply to. And if that tavle has an entry that satisfies the where clause then assign the appropriate error message to @ErrMessage.
I understand sp_executesql might do the trick because it allows passing local params back and forth.
Any ideas on how to make this work? I appreciate the effort.
View 3 Replies
View Related
Sep 18, 2007
Hi,
I have written a stored proc to bulk insert the data from a data file.
I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc
The follwing statement works fine if i give the table name directly in the query
Code Snippet
DECLARE @LastUpdate varchar(20)
SET @LastUpdate = 'Dec 11 2007 1:20AM'
INSERT INTO Category
SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt',
FORMATFILE = '\remotemachineFormatFile.fmt',
FIRSTROW = 2) AS a
To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below
Code Snippet
@Category - Will be passed as a parameter to the stored proc
DECLARE @vsBulkSQL VARCHAR(MAX)
DECLARE @LastUpdate varchar(20)
SET @LastUpdate = 'Dec 11 2007 1:20AM'
SELECT @vsBulkSQL ='INSERT INTO '+ @Category + ' SELECT MSISDN, ''' + @LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\remotemachinedatafile.txt'+ ''''+ ' ,' +
+ ' FORMATFILE ' + '=' + ''''+ '\remotemachineFormatFile.fmt'+ ''''+ ',' +
' FIRSTROW ' + '=' + '2' + ')' + ' AS a'
Print @vsBulkSQL - This prints the folliwing statement
INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a
Exec @vsBulkSQL - This statement gives the following error
The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.
Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same
~Mohan
View 4 Replies
View Related
May 31, 2005
Basically, I have a table with a column that stores mathematical formulas in string format. When my UDF is executed, it needs to select an appropriate formula from this table and evaluate it using values that are stored in local variables.
Look at the example below:
Suppose I have a string named @vcFormula that contains the following:"@dVar1 + @dVar2 / @dVar2"Now suppose I have a variable named @dVar1 that contains a value of 1.0, and variable @dVar2 contains a value of 2.5. I can use the REPLACE function to change my original string to look like this:"1.0 + 2.5 / 2.5"
Now I want to execute this string and find the numeric result, placing it in a variable named @dResult. The following works, but presents a problem:CREATE TABLE #Result (dResult decimal(20, 10))INSERT #Result EXEC('SELECT ' + @vcFormula)SELECT @dResult = dResult FROM #ResultThe problem with using this method comes from the fact that I need to be able to evaluate @vcFormula from within a user-defined function, but temporary tables are not allowed inside UDF's.
So I attempted to change the temporary table above into an instance of the TABLE data type. This didn't work either because EXEC cannot be used to populate instances of the TABLE data type. Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's. Specifically, is there any way to execute a command/formula that is contained within a string other than by using EXEC?
View 10 Replies
View Related
Sep 19, 2000
How do I use a @variable to hold on value return from an exec ('string command') statement.
Example for:
declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)
set @db = 'DBNAME'
set @OldOwner = 'USERNAME'
select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
exec (@cmd)
How can I use @OldID to hold on UID return from this statement.
When I try use:
select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
then I'm getting a error message: @OldID not declare.
Thanks.
View 2 Replies
View Related
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
May 5, 2008
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 7 Replies
View Related
Aug 20, 2007
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 7 Replies
View Related
Mar 27, 2007
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 1 Replies
View Related
Jan 26, 2006
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 4 Replies
View Related
Sep 28, 2006
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 3 Replies
View Related
Mar 7, 2008
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 14 Replies
View Related
Jan 28, 2014
I have a concated code from excel when I have to pick some values for db update, the problem is when one field is empty, this field is date type and I don't now how can I validate if UserDate_02 field is null or not, I get it from a cell in excel and later when I get all the code I update the db, this is the line :
UPDATE ITEMS SET UserField_01='AA',UserDate_01='01/28/2014',UserYesNo_01=1,UserDate_02=''WHERE LotNumber='134034143017297'and ItemCode='G22221'
View 2 Replies
View Related
Mar 23, 2008
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 5 Replies
View Related
Sep 21, 2005
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 6 Replies
View Related
Mar 28, 2006
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 1 Replies
View Related
Mar 10, 2008
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 5 Replies
View Related
Oct 18, 2006
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 2 Replies
View Related
Oct 13, 2006
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 1 Replies
View Related
