Any SQL Gods Out There????
Oct 4, 2004
I have a SP that I am trying to finalize however; my inexperience is showing itself on this one.
History:
3 Tables: Tooldb - Employee - ToolUserdb
Scenario:
I have a webform in c# that gathers data concerning internal tools(applications) that are written in-house. One of the fields is a listbox of names pulled from the Employee table called Creator. When the form is submitted I need to have the list of selected employees published to the ToolUserdb.
My SP:
ALTER PROCEDURE dbo.InsertTool
(
@ToolNamenvarchar(250),
@Platformnvarchar(250),
@Vendornvarchar(250),
@Subplatformnvarchar(250),
@Submitternvarchar(250),
@Finders nvarchar(250),
@LTDnvarchar(50),
@JobAreanvarchar(250),
@Funcnvarchar(250),
@Ownersnvarchar(250),
@Activenvarchar(250),
@Version numeric,
@Buildnumeric,
@CWSTDnvarchar(50),
@Statusnvarchar(250),
@Costnumeric,
@Notesnvarchar(250),
@Keywordsnvarchar(250),
@Linksnvarchar(250),
@Pathsnvarchar(250),
@eidint,
@tdbidint,
@Creator nvarchar(250)
)
AS
INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
VALUES
(@ToolName, @Platform, @Vendor, @Subplatform, @Submitter, @Finders, @LTD, @JobArea, @Func, @Owners, @Active, @Version, @Build, @CWSTD, @Status, @Cost, @Notes, @Keywords, @Links, @Paths)
INSERT INTO ToolUsersdb (@Creator) SELECT + @eid + ',[ID] FROM Employee + @tdbid + ',[ID] FROM ToolDB IN (@Creator)
Error:
Incorrect syntax near keywork IN (referring to last insert statement).
Can someone tell me how I can get this multi-insert stmt. to work?
Thank you!
Tim
View 8 Replies
Jul 20, 2005
HiI'm having trouble with the script below that it just won't do acorrect ORDER BY for a date field.When executing the two SELECT TOP statements on their own the recordsare sorted correctly but when wrapped in the main statement, the ORDERBY is just ignored. Tried to play around with the script but with noluck. Script is for paging, in this case, select all records meetingcriteria, order and select Top 25 to display on first page. Anyquestions, please don't hesitate contacting me.Thanks very much to anyone who's having a look at this!MartinSELECT COALESCE((i2b_contact.Firstname + CHAR(32) +i2b_contact.Lastname),i2b_company.CompanyName) AS CName,i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,(i2b_address.Address1) AS PropertyAddress,A.ProgUserName AS ProgUserName,CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) ASTransactionDate,CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) ASReturnByDateFROM i2b_keytransactionlogLEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =A.ProgUserID)WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROMi2b_keytransactionlogLEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =A.ProgUserID)WHERE i2b_keytransactionlog.ProgClientID = 1 ANDi2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN(SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlogLEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =A.ProgUserID)WHERE i2b_keytransactionlog.ProgClientID = 1 ANDi2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)ORDER BY ReturnByDate DESC)ORDER BY ReturnByDate DESC
View 1 Replies
View Related
Jul 20, 2005
HelloI'm desperate for help with the following dynamic SQL. It used to work forages but suddenly stopped working today! I can't recall changing anything ofimportance.. So I say. Anyway, I'm getting this error: "Cannot use emptyobject or column names. Use a single space if necessary."I've identified the location within the script that causes this message it'sthis line:(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,3rd line of Set @cmdSQL =.I've been trying to insert a single space between "" which eliminates halfof the error but I can't figure out what quotes to use around 'MG' and 'MA'.I'd be grateful if you can have a look at this and let me know how tocorrect this problem.Like I said it used to work and I'm perplexed about this sudden error. Isthere any change that can cause this behaviour?Many thanks for your efforts!!Have a nice day!!MartinPaging Script:CREATE PROCEDURE dbo.sp_ListKeyOut(@page_number INT,@number_of_records INT,@cmdWHERE VARCHAR(200),@cmdORDERBY VARCHAR(200)) ASSET NOCOUNT ONDECLARE@SizeString VARCHAR(5),@PrevString VARCHAR(5),@cmdSQL varchar(2000)SET @SizeString = CONVERT(VARCHAR, @number_of_records)SET @PrevString = CONVERT(VARCHAR, @number_of_records * (@page_number - 1))SET QUOTED_IDENTIFIER OFFSET @cmdSQL = 'SELECT COALESCE((i2b_vw_contact.Firstname + CHAR(32) +i2b_vw_contact.Lastname),i2b_vw_company.CompanyNam e) AS CName,i2b_vw_keytransactionlog.KeyTransactionLogID,i2b_vw_keytransactionlog.KeyID,(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,i2b_vw_address.Address1 AS PropertyAddress, i2b_vw_contact.MobileNo,A.ProgUserName AS ProgUserName,CONVERT (varchar(10), i2b_vw_keytransactionlog.TransactionDate, 104 ) ASTransactionDate,CONVERT(varchar(10),i2b_vw_keytransactionlog.Retur nByDate,104) ASReturnByDateFROM i2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE KeyTransactionLogID IN'IF @cmdWHERE IS NULL OR @cmdWHERE = ''BEGINEXEC(@cmdSQL +'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE KeyTransactionLogID NOT IN(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)ORDER BY ' + @cmdORDERBY + ')ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY)-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROMi2b_vw_keytransactionlog')ENDELSEBEGINEXEC(@cmdSQL +'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE ' + @cmdWHERE + ' AND KeyTransactionLogID NOT IN(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE ' + @cmdWHERE + ' ORDER BY ' + @cmdORDERBY + ')ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY)-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROMi2b_vw_keytransactionlog WHERE ' + @cmdWHERE)ENDSET QUOTED_IDENTIFIER ONRETURN 0GO
View 4 Replies
View Related