Web Development

Ask something
Question Locked

Sql Syntax

0 helpful
2 replies

by evga

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am writing a Stored Procedure to execute a Script with If then Statements. I am having a Hard time with the Inital Select Statment at the end where I am trying to compare a variable in the Statement. There should be a way to write this I just don't know how?

SET @SearchSQL = 'Select FD__MED_SURR.Today_date, FD__MED_SURR.Med_Name, FD__MED_SURR.MED_QU, FD__MED_SURR.MedAction, FD__MED_SURR.Docaction , FD__MED_SURR.OP__DOCID, FD__MED_SURR.Clientkey, FD__MED_SURR.Admissionkey From FD__MED_SURR Where @Clientkey = Clientkey and @Admissionkey = Admissionkey'

at the end of the stored proc it executes the @SearchSQL

2 replies

by jgarcia102066

You have to cast your variables as some type of string variable such as varchar and then surround them with the appropriate delimiters.

If your @Admissionkey and @Clientkey are integers this should work. If the integer values are greater than 10 places then increase the 10 to accommodate.

SET @SearchSQL = 'Select FD__MED_SURR.Today_date, FD__MED_SURR.Med_Name, FD__MED_SURR.MED_QU, FD__MED_SURR.MedAction, FD__MED_SURR.Docaction , FD__MED_SURR.OP__DOCID, FD__MED_SURR.Clientkey, FD__MED_SURR.Admissionkey From FD__MED_SURR Where ' + CAST(@Clientkey AS VARCHAR(10)) + ' = Clientkey and ' + CAST(@Admissionkey AS VARCHAR(10)) + ' = Admissionkey'

If the variables are some type of string variable then use the following:

SET @SearchSQL = 'Select FD__MED_SURR.Today_date, FD__MED_SURR.Med_Name, FD__MED_SURR.MED_QU, FD__MED_SURR.MedAction, FD__MED_SURR.Docaction , FD__MED_SURR.OP__DOCID, FD__MED_SURR.Clientkey, FD__MED_SURR.Admissionkey From FD__MED_SURR Where ''' + @Clientkey + ''' = Clientkey and ''' + @Admissionkey + ''' = Admissionkey'

This discussion is closed to new replies.