Function Viability 1

[WMitchellCPQ]

Hi again SQL Coders thanks for you previous help
Quick one .. Im just want to know if its possible or not there is no need to show me how any way...

Is this possible forgive my t-SQL like pseducode

create procedure sp_addproduct
var @producttype
var @productdata

//productdata will be in the following format
fieldname|data|fieldname|data

is it possible to create a sp to do an INSERT variable statement depending on the fieldnames ? Can T-SQL parse strings sufficiently ??

Any help is massively appreciated as always

Will

 

[Moptop]

Hi

T-SQL does have string handling capabilities. I have previously written code to parse CSV files, etc, all in T-SQL.
Check out the LEN function (remember, there is a gotcha if you have spaces on the end of a string), the CHARINDEX function and the SUBSTRING function.

That should be all you need.

Remember though, if you are building dynamic SQL statements, they can be quite slow, so build a Stored Procedure to wrap the database statements.

I hope this helps

Mark

 

[WMitchellCPQ]

Hi Mark or anyone else can you help me debug the following function. The Initial insert works but the UPDATE doesnt seem to work any help appreciated.

CREATE PROCEDURE dbo.sp_Orders_InsertProposedOrderItems(
@OrderID [int],
@ProductID [int],
@Quantity [int],
@DateRequired [datetime],
@modStateId [int],
@ProductData varchar(3000),
@tmpSeperator char(1)
)

AS

/* Declare local variables */
DECLARE @tmpFieldName VARCHAR(50) /* Holds Current FieldName */
DECLARE @tmpFieldValue VARCHAR(50) /* Holds Current FieldValue */

/* Pad with an ending % for the while loop */
SELECT @ProductData = @ProductData + '%'
/* ProductData will look like the following */
/* FieldName3|FieldName3Data|FieldName5|FieldName5Data|FieldName8|FieldName8Data% */

INSERT INTO dbo.OrderItems
                      (OrderID, ProductID, Quantity, DateRequired, modStateId)
VALUES     (@OrderID, @ProductID, @Quantity, @DateRequired, @modStateId)

WHILE (CHARINDEX(@tmpSeperator,@ProductData) > 0)
BEGIN
/* Get the fieldname from the list */
	SELECT @tmpFieldName = SUBSTRING(@ProductData,1,(CHARINDEX(@tmpSeperator,@ProductData)-1))
/* Trim that text from the list */
	SELECT @ProductData = SUBSTRING(@ProductData,(CHARINDEX(@tmpSeperator,@ProductData)+1),3000)
/* Get the fieldvalue from the list */
	SELECT @tmpFieldValue = SUBSTRING(@ProductData,1,(CHARINDEX(@tmpSeperator,@ProductData)-1))
/* Trim that text from the list */
	SELECT @ProductData = SUBSTRING(@ProductData,(CHARINDEX(@tmpSeperator,@ProductData)+1),3000)


UPDATE OrderItem
SET @tmpFieldName = @tmpFieldValue
WHERE OrderID = @OrderID


END
GO

Will

 

[cheyney]

UPDATE OrderItem
SET @tmpFieldName = @tmpFieldValue
WHERE OrderID = @OrderID


you're setting a variable equal to itself.
This should be a column equal to a variable

Cheyney

 

[WMitchellCPQ]

No its tmpFieldName = tmpFieldValue
But thanks for reading