Converting nvarchar to int datatype from a select query stored procedure

Question

I had this problem on converting nvarchar datatype from a select query using stored procedure into Integer datatype and insert it into my database;

I already search the internet many times on how to solve my problem but, the codes i found was not able to solve my problem.

This is my code,

USE [SLCBRegistrarDB]
 GO
 /****** Object:  StoredProcedure [dbo].[CountLabfees]    Script Date: 
 10/30/2017 7:06:43 PM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 ALTER Procedure [dbo].[CountLabfees]
 @LabSubject nvarchar(50)

 as

 BEGIN
 SET NOCOUNT ON;
 Declare @SQL NVARCHAr(MAX)
 SET @LabSubject = REPLACE(@LabSubject,',',''',''')
 SET @SQL='SELECT Distinct(TESTFeeSETUP.[Fee Code])
 FROM TESTFeeSETUP INNER JOIN TESTFeeSETUPSubTable ON TESTFeeSETUP.[Fee 
 Specification] = TESTFeeSETUPSubTable.[Specification ID]
 WHERE TESTFeeSETUPSubTable.Specification in (''' + @LabSubject + ''')'

 Declare @Quantity NVARCHAr(MAX)
 SET @Quantity='SELECT Count(FEESList.[Fee Description]) AS Quantity
 FROM FEESList INNER JOIN TESTFeeSETUP ON FEESList.FeeID = 
 TESTFeeSETUP.FeeID INNER JOIN TESTFeeSETUPSubTable ON TESTFeeSETUP.[Fee 
 Specification] = TESTFeeSETUPSubTable.[Specification ID]
 WHERE TESTFeeSETUPSubTable.Specification in (''' + @LabSubject + ''')'

 Declare @Total NVARCHAr(MAX)
 SET @Total='SELECT Distinct(TESTFeeSETUP.[Amount/Cost])
 FROM TESTFeeSETUP INNER JOIN TESTFeeSETUPSubTable ON TESTFeeSETUP.[Fee 
 Specification] = TESTFeeSETUPSubTable.[Specification ID]
 WHERE TESTFeeSETUPSubTable.Specification in (''' + @LabSubject + ''')'

 EXEC (@SQL)
 EXEC (@Total)
 EXEC (@Quantity)
 END
 BEGIN
    INSERT INTO FeesStudentBILLING VALUES(10,@SQL,@Quantity,@Total,1,1)
 END

Everytime I execute the code, this error always appears,

"Cannot convert nvarchar datatype 'SELECT Count(FEESList.[Fee Description]) AS Quantity FROM FEESList INNER JOIN TESTFeeSETUP ON FEESList.FeeID = TESTFeeSETUP.FeeID INNER JOIN TESTFeeSETUPSubTable ON TESTFeeSETUP.[Fee Specification] = TESTFeeSETUPSubTable.[Specification ID] WHERE TESTFeeSETUPSubTable.Specification in (''' + @LabSubject + ''')' into integer datatype"

I am new to programming, so I really need some help on this please. thanks

It seems like your `[Fee Specification]` is not int. try `CAST([Fee Specification] as int)` — Prabhat G, Oct 30 '17 at 13:37
Why did you use the dynamic query? It would be much more simple if you just implement the normal query. Show me the FeesStudentBILLING table schema, I will show you how to do that. — Edward N, Oct 30 '17 at 13:51
@Edward I was trying to find a way where i have to select a specific primary key with IN clause.. Its hard for me to demonstrate, if you could just take a closer look at the code i made., i'm struggling in it, i really badly need some help. — deitysha, Oct 30 '17 at 14:27

Edward N · Answer 1 · 2017-11-02T14:41:18.630

0

It's so much easier for you, if you do something like:

Using the split string function to get values from your @LabSubject, and then stored them in local variable table.
Using the local variable table in step 1 with your condition query.

For example (SQL Server 2016)

    DECLARE @LabSubjectTable TABLE(Value NVARCHAR(50))

        INSERT INTO @LabSubjectTable 
        SELECT value FROM STRING_SPLIT(@LabSubject, ',')


         SELECT Distinct(TESTFeeSETUP.[Fee Code]) AS FeeCode
FROM TESTFeeSETUP AS t1
INNER JOIN TESTFeeSETUPSubTable ON TESTFeeSETUP.[Fee Specification] = TESTFeeSETUPSubTable.[Specification ID]
WHERE TESTFeeSETUPSubTable.Specification in (SELECT value FROM @LabSubjectTable)


 SELECT Count(FEESList.[Fee Description]) AS Quantity
 FROM FEESList INNER JOIN TESTFeeSETUP ON FEESList.FeeID = TESTFeeSETUP.FeeID 
 INNER JOIN TESTFeeSETUPSubTable ON TESTFeeSETUP.[Fee Specification] = TESTFeeSETUPSubTable.[Specification ID]
 WHERE TESTFeeSETUPSubTable.Specification in (SELECT value FROM @LabSubjectTable)


 SELECT Distinct(TESTFeeSETUP.[Amount/Cost]) AS AmountCost
 FROM TESTFeeSETUP INNER JOIN TESTFeeSETUPSubTable ON TESTFeeSETUP.[Fee Specification] = TESTFeeSETUPSubTable.[Specification ID]
 WHERE TESTFeeSETUPSubTable.Specification in (SELECT value FROM @LabSubjectTable)

Updated:

I think you can get the ideas now. Based on that, you can update according to your logic.
If your SQL Server does not support built in split string function, you can create one T-SQL split string

edited Nov 02 '17 at 14:41

answered Oct 30 '17 at 14:03

Edward N

997
6
11

But my problem is, How about the @LabSubject? How can i set multiple values in my IN cluase and how will I going to execute my code with that query? – deitysha Oct 30 '17 at 14:41
Whats with the INSERT query? what does it do with the select query? I think my problem is how to set multiple values into single variable.. i was able to do it with the code i provided but it does not convert it to Integer. Can you explain it more for me how to do it.? – deitysha Oct 30 '17 at 15:38
@Edward..I had this error while editing the code "Incorrect syntax near 'TESTFeeSETUP'. Expecting '(', or SELECT". Please help. – deitysha Nov 02 '17 at 13:23
Hi, it's really hard to give you a full query without knowing about logic. I think your first problem is solved. If you are facing problem to develop a query, I think you can ask another question, and provide sample data for each table, as well as an expected result, it will easier to everyone to help you out. – Edward N Nov 02 '17 at 14:46
But can you help me if i'll be giving you the whole scenario? Including my database structure? – deitysha Nov 02 '17 at 14:51
@deitysha you should post it, so anyone can help out – Edward N Nov 02 '17 at 16:03
I already posted my question but i've changed the title..please check on it – deitysha Nov 02 '17 at 16:05

Converting nvarchar to int datatype from a select query stored procedure

1 Answers1