ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
how can I pass the variable @item correctly for the string comparison
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/* ** fn_FindReports.sql ** ** This multi-statement table-valued user-defined ** function takes an EmplyeeID number as its parameter ** and provides information about all employees who ** report to that person. */ USE ClassNorthwind GO /* ** As a multi-statement table-valued user-defined ** function it starts with the function name, ** input parameter definition and defines the output ** table. */ CREATE FUNCTION fn_FindReports (@InEmployeeID char(5)) RETURNS @reports TABLE (EmployeeID char(5) PRIMARY KEY, Name nvarchar(40) NOT NULL, Title nvarchar(30), MgrEmployeeID int, processed tinyint default 0) -- Returns a result set that lists all the employees who -- report to a given employee directly or indirectly AS BEGIN DECLARE @RowsAdded int -- Initialize @reports with direct reports of the given employee INSERT @reports SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0 FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN -- Mark all employee records whose direct reports are going to be -- found in this iteration UPDATE @reports SET processed = 1 WHERE processed = 0
-- Insert employees who report to employees marked 1 INSERT @reports SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0 FROM employees e, @reports r WHERE e.ReportsTo = r.EmployeeID AND r.processed = 1 SET @RowsAdded = @@rowcount -- Mark all employee records whose direct reports have been -- found in this iteration UPDATE @reports SET processed = 2 WHERE processed = 1 END RETURN -- Provides the value of @reports as the result END GO
hi there, i am trying to pass a string which contains a string, here is the code which is wrong : {string sqlcommand = "select pnia.pnia_number, pnia.user_name, pnia.date_pnia, pnia.user_pnia, problem.problem, gormim.gorem_name, status.status_name from pnia,gormim,problem,status where (pnia.status='@p1' and status.status='@p1' and pnia.problem=problem.problem_num and pnia.gorem=gormim.gorem)"; OleDbCommand cmd = new OleDbCommand(sqlcommand,con);OleDbParameter p1 = new OleDbParameter("@p1",this.DropDownList4.SelectedItem.Value.ToString()); cmd.Parameters.Add(p1); } the problem is that the sql compailer doesnt take the parameter (@p1) as a string if someone could help me with that it would be great ! tnx
I have cursor that loops through a table (the table only contains columnnames of several tables) the cursor has a variable declared @columnname. when i run the following it works fine
now i want to get the min of each column name like so
select @columnname,min(mtr_5120),0,0,0 from temp_prt ------> this works for min(mtr_5120) mtr_5120,34.5,0,0,0
now I want to generalize so I try to pass in the variable name and I do the following
select @columnname,min(@columnname),0,0,0 from temp_prt (the columname (@columnname) exists in the table temp_prt)
but now i get an error Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.how can i pass the colunmame into the min and max functions or is that at all ppossible. I also tried the following:
select @columnname,'min(' + @columnname + ')',0,0,0 from temp_prt
but i get the same error Msg 8114, Level 16, State 5, Line 29 Error converting data type varchar to decimal.
Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.
There is a MAIN table: ItemNo int identity(1,0), ItemType tinyint
There is a WETPAINT table: ItemNo int, Color varchar(20)
There is a DRYPAINT table: ItemNo int, Color varchar(20)
Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType
So I created a table function called getTable:
CREATE FUNCTION [dbo].[gettable] ( @ItemType int = 1 ) RETURNS @thistable TABLE ( Color varchar(20)
) AS BEGIN if @ItemType = 1 insert into @thistable (color) select color from WETPAINT if @ItemType = 2 insert into @thistable (color) select color from DRYPAINT RETURN END
This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:
SELECT MAIN.ItemNo, a.Color FROM MAIN INNER JOIN gettable(Main.ItemNo) as a ON a.ItemNo = MAIN.ItemNo
Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.
Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.
PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.
i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.
The syntax is as follows:
select car.id, car.licenceNumber, car.brand, car.model, (select driverName from getCurrentDriver(car.id)) as driverName from car
When I try to compile I get following error on the line of the function: Incorrect syntax near '.'
The database version is SQL Server 2000 SP3.
What am I doing wrong? Is there a workaround for this error?
I am getting an invalid cast specification when I call dtexec and try to /SET a user variable within the package that is defined as a string data type. Is there any solution to this? I have tried passing the GUID with {} w/o {} w/ '' w/ "" etc.. many variations... and still get an invalid cast specification. Is there a data type that I need to set the User variable to besides String? The User Variable is used to Select records from a SQL data source where the GUID is stored. I do not have an option of GUID data type for a User Variable.
I'm running into this error message when passing in a few records in particular to a function, the only difference I could find is that these recods have about 60k characters on the field that I'm passing to a function.
is there a max lenght for passing to a function?
select function ( field) as results
It's been working fine until today and all of the related fields are declared as nvarchar(max)
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
I have a parent package that contains two children... The second child depends on the succes of the first child.
THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...
Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...
I tried to execute coth children as Out Of Proc as well as In Proc...The same
Would anybody have an idea how to resolve this problem?
I have a table with 52 columns named 'Week1', 'Week2' etc. with values 1, 0 etc. I want to check values in each column. I have following lines in my procedure.
Declare @l_str varchar(50), @l_count int
Select @l_count = 1 Select @l_str = 'Week' + Convert(varchar, @l_count) Now how do I compare the value stored in the @l_str which should be wither 0 or 1 and not 'Week1'?
Is there any better method to compare read these 52 table variables?
Hi I wanna put string in query "where" part.For example,$sql="select VEHICLEFROM databaseWHERE MECHANIC =BRIAN";like above, "mechanic" column is filled with strings. Then how can Iwrite "where" part?Above query does not work.Thanks.
Ok, here is what i'm trying to do and its driving me nuts.
ok,
1) I have a proc that runs and needs to validate the user prior to running - this proc is called from an hand held device
2) the id and password are being passed as "clear text" but the password is stored in the database table hashed.
Is there anything on the db side that can get the hash value from the password column of the aspnet_membership table and compare it to the password being passed in to this proc? I have suggested several options to the handheld developer but nothing. This has to be done on the database side.
so,
username and password are passed to proc from handheld.
proc needs to validate ther user in the aspnet_membership table
if user id and password are valid execute the stored procedure
is this possible? if so can ANYONE point me to some examples of this being done?
Hi, This is pretty simple but I am doing somethin really stupid somewhere, I am trying to pass a value to a variable declared in a package using some SQL code, I declared a variable "var1" and assigned the scope to the package , then I dropped a execute SQL task within the package and wrote a simple sql code that would get the maximum id from a table, "select max(id) from table" , now I want to assign the variable this max value.so in the parameter mapping tab I select the variable give it a parameter name of 0 and size of -1 along with direction of "output". everythin looks simple but when I try to use this variable value in my next execute sql task it behaves weirdly, I am trying to update anothet table using this variable value, so I add another task and put this code in there " update table2 set id = ? " , and on the parameter mapping tab I set the direction as input, however when I look at the column it updated th column with a weird number , xomethin like "230072408" which doesnt mean anything. I know that tha max value of ID is 10. Please help.
I have used a query statement with the following WHERE string to 'Fill' a dataset. "AND (A.ApptsDate > '" & strApptPreDate & "' OR (A.ApptsDate = '" & strApptPreDate & "' AND A.ApptsTime >= '" & strApptPreTime & "' ))" & _ and strApptPreTime is defined as:Dim strApptPreTime As String = SomaShared.strPadTime(CStr(dApptCalcNewDate.Hour) + ":" + CStr(dApptCalcNewDate.Minute))
Somehow, the dataset showed only the ApptsTime after 10 am. After more than 2 weeks of debugging, I still can see a dataset watch for > 10 amAppsTime only. Now I am guessting, the problem is 9 is different from 10 - 16, it all because 9 is single digit. Then I check the data type settings for these variables. Here are what I found: In SQL Server Agent job, the ApptsTime data was 'inserted' by @NewApptTimes, which is declared as char(5).In SQL Server database, the ApptsTime was defined as nvarchar(15). My question are: 1. The reason why there were no 9 am data for the dataset, is becasue 9 am of nvarchar(15) is not > 8:30 of strApptPreTime? 2. If the answer to the quation 1 is yes, how do I define AppsTime and/or strApptPreTime? TIA,Jeffrey
Hi All, I have a column in my table like so: 'D4B00 L2A00 L3A00 L6C00 P1C00 L2A28 P4B00 ' How do I check in SQL if any pieces have the first 3 character the same. In the above case, L2A is present twice. I need to do this because I need display disctinct items, therefore L2A needs to be displayed only once. Any help is appreciated. Thanks
CREATE FUNCTION [dbo].[Compare] ( @usrVal varchar(100), @dbVal varchar(100) ) RETURNS BIT AS BEGIN DECLARE @flag BIT DECLARE @userStrLen INT DECLARE @dbStrlen INT DECLARE @counter INT --Default assignments SET @counter=1 Set @userStrLen=len(@usrVal) Set @dbStrlen=len(@dbVal)
IF (@userStrLen != @dbStrlen) BEGIN return 0 END ELSE BEGIN WHILE @counter <= @userStrLen BEGIN IF (substring(@usrVal,@counter,1) != substring(@dbVal,@counter,1)) RETURN 0 SET @counter = @counter + 1 END return 1 END RETURN @flag --SELECT dbo.Compare('sqlserver user defined functions','sqlserver is the best backend') END
Hi,I have a funny Error in our sql server 7 & 2000. When I compare twostring in Unicode format. I receive that it is the same when I add newnchar.I don’t understand why?Could you help me ?declare @str nvarchar(128), @str2 nvarchar(128)Set @str =nchar(21121)+nchar(49352)+nchar(47811)+nchar(48256 )+nchar(4966)+nchar(25736)+nchar(1788)+nchar(51220)+nchar(17733)Set @str2 =nchar(21121)+nchar(49352)+nchar(47811)+nchar(48256 )+nchar(4966)+nchar(25736)+nchar(1788)+nchar(51220)+nchar(17733)+nchar(4 1273)select @strselect @str2SELECT DIFFERENCE(@str, @str2)if soundex(@str) = soundex(@str2)BEGINSELECT 'OK'ENDELSEBEGINSELECT 'KO'ENDif @str = @str2BEGINSELECT 'OK'ENDELSEBEGINSELECT 'KO'END-=zoltux=-*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi All, Would somebody be able to help me from pulling my hair out!??I have a form with a radiobuttonlist. I would like to change my select statement depending on what radiobutton value is selected.E.g.SELECT * FROM table WHERE <<variable from radiobuttonlist>> LIKE 'Y'So,if radiobutton value selected = 1, it will select * from column A in the dbif radiobutton value select = 2, it will select from column B in the dband so on... Am i attempting to do the impossible?Thanks All,
Hello, I have a SP that recevies a date value for a users date of birth called "dob". However when passing it into the class which contains the Stored procedure it gives an error. Below is my code please advice Thanks Ehi
HERE IS THE ERROR MESSAGE Compiler Error Message: CS0103: The name 'dob' does not exist in the current context
Source Error:
Line 40: command.Parameters[2].Value = "First_Name"; Line 41: command.Parameters[3].Value = "Last_Name"; Line 42: command.Parameters[4].Value = DateTime.Parse(dob.Text); Line 43: Line 44: int i = command.ExecuteNonQuery();
I am trying to get data from a database through a select statment and I want to pass the return query to a c# string variable. Any idea how can I do this?
could anyone please help me to resolve this issue? here's my sql query which retrieve last 3 month data t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'") now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) ) i need to pass a variable to retrieve data based on user requirements. i tried this way, dim mno as n mno=4 t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'") its not working. can i achieve this using stored procedure? or can i directly pass a variable to sql synatax? thanks for any help
hi guys what is the syntax for using the passing variable part into the name of a table in a store procedure. in particular: (assume already declared the variable periodseq.
select * into Temp_Usage_@periodseq from Master_usage where Master_usage.PERIODSEQ = @periodseq
in particular the Temp_Usage_@periodseq line of code, how do i "add" the periodseq (which is a number) to the end of the name of Temp_Usage, i.e: Temp_Usage_112 is the syntax an & like Temp_Usage_&@periodseq? Cheers Champinco
I've been coding a few years and SSIS makes me feel more stupid than any program I've ever used. I've read BOL and bought a book. Can't say either one has really helped.
I'm still a complete idiot after one week of working with it. I apologize for asking so many stupid questions.
What I'm trying to do now is parameterize a SQL statement.
I have a variable that's a string. I have a DataFlowComponent as a data source. I find references all over the Internet and this forum to something called an "ExecuteSQLTask" but I sure can't figure out what that is. In my toolbox I have data flow sources for DataReader, Excel, Flat Files, Old DB, Raw file, and XML but no Execute Sql Task.
Anyway SELECT * FROM TABLE WHERE COLUMN='Value' in the SQL Command property is simple enough. Now I want 'Value' to be a variable. You know, like in T/SQL DECLARE @Foo VarChar(25). Creating the variable is easy as pie.
I have found at least 10 different examples of specifying variables on the web, all of which claim to be SSIS examples, Is it User::Variable? @[User::Variable]? @Variable?
I want to read rows from a table, with a a variable value in the WHERE clause, and pass them to the fuzzy lookup task.
Hi I am extracting data from Oracle via SSIS. There are three smilar schemas from where the data has to be extracted. Say My query is " Select * from abc.dept" where abc is the schema name. I want to pass this schema name through a variable and it should loop as there are total 3 schemas. There is a table which provided list of schemas. Can somebody please guide me how to do this. There are multiple references of this table is SSIS package.
I am running a query in with my daily import job that lets me know if a duplicate record was imported into the system. If so I want to setup my email table to mail me the following information. My code works fine except the value of the variable @ID is only giving me the first record in the set. How do I pass it on to the next record? I get the right number of rows inserted into the email table but the @ID variable is the same for all 3.
Drop Table #SER1
Select id#,
Colx=Count(*)
Into #SER1
From Business
Group By id#
Having Count(id#) > 1
Declare @ID Varchar (4)
Select @ID = ID# from #SER1
INSERT INTO Email
( [To],
[Subject],
[message],
[Table],
TableUKey,
TableSource
)
SELECT
'Firstname.Lastname@Company.com',
'Duplicate ID# Imported',
'ID# ' + @ID + ' has been imported multiple times ' + ' on ' + cast( getdate() AS varchar(30)) + '. You need to remove the record and rerun the import.',
